We don't have a DBA and I have to do backups. We have only one database that is 3gb (the others are less than 100mb). The 3gb database is write-heavy, and has very important data. I set up daily backups for all the databases but i think that might not be enough.

  • What are transactional log backups?
  • what is my appropriate backup action plan for the 3gb database?
  • how do i do the log backups and for what purpose?
  • is a daily full backup enough?

migrated from stackoverflow.com Oct 27 '10 at 16:49

This question came from our site for professional and enthusiast programmers.

  • Ultimately this is a business question first and foremost. What is the business policy regarding data backups, retention, loss, and recovery? – joeqwerty Oct 27 '10 at 17:07

Ok, you really need to head over to the documentation and read it - become a small dba. Expecting people here to copy / paste the EXTREMELY detailed information from BOOKS ONLINE - the SQL Server documentation - is not good behavior. That said, you also areon the wrong site - this is so absolutely NOT a programming issue. There is a sister site (serverfault.com) for server operational issues, which backups belong to.

To get you started:

The transaction log records all changes done to the Database. THis means you take a full backup, then the tx log can be used to roll that forward up to THE LAST TRANSACTION BACKED UP IN THE LOG. MEans that if you take a backup of the tx log afte 16 hours, the server dies, on the new server you restore the daily backup, then process the transaction log and are back to the last commited transaction after 16 hours ;) If you ask me - a business not doing that deserves the damage they get from not doing that.

The transaction log actually is the HUGH advantage you have in something like SQL Server compared to classical file level backups.

All other questions I can not answer. Seriously. These are not "dba level" decisions, they are business decisions. I know companies doing transaction log backups every 5 minutes, shipping them over to a seaprate server (check: Log File Shipping). Reason: Loosing data would be a desaster. Imagine Amazon loosing all sales for half a day. I know other businesses doing daily, sometimes even weekly backups (small shop, intranet site). I know others not relying on backups for desaster issues, but using replication and / or mirroring, with daily full backups and hourly log backups, so that if a server dies, they dont get any downtime. All this is "the same" from a technical point of view - every recommendation depends on the business case, which you say nothing about.

As a normal scenario I would suggest regular full backups (weekly, during off time like sunday), daily differential bakups (a lot smaller than the full one) and then a log backup every x hours (1, 6, 12 - depends on your business case).

  • +1 for the business decision statement. What good is a 15 minute log backup if the business requires 5 minutes? The business needs have to be addressed first. – joeqwerty Oct 27 '10 at 17:27

The others have given you some basic overview. I'm going to give you some generic answers to your questions, but note that the actual answers will depend on your business needs.

  • What are transactional log backups?

Transaction log backups backup the transaction log. This log records every transaction performed on the database. When a database is in Simple recovery mode, this log is cleared after every Checkpoint on the DB. When the database is in Full mode, these logs continue to fill with transactions until backed up or manually truncated. By backing up the transaction logs, you can use them in combination with "regular" database backups to roll forward to recently performed transactions when recovering the database.

  • what is my appropriate backup action plan for the 3gb database?

There is no one answer to this question, but in general I tend to do a weekly Full backup, combined with daily Differential backups.

  • how do i do the log backups and for what purpose?

The log backups will be performed by a SQL Maintenance Plan that you create. You perform these backups to clear the transaction log, and record the transactions in case you need to recover them. Please note that if you don't intend to use transaction log backups, you should change your database to Simple recovery mode to avoid issues will full logs or full disks, as the transactions will not auto-clear from the log when in Full recovery mode.

You can perform the log backups with whatever frequency is necessary for your application. For example, if it's unacceptable to ever lose more than 5 minutes of transactions, you'd perform log backups every five minutes, in combination with your regular full and differential database backups.

  • is a daily full backup enough?

That depends entirely on your needs. My standard is weekly Full plus daily Differential, and then log backups every 15 to 30 minutes for Full recovery databases, or on another schedule if required. Another decision you'll need to make is how long to retain your backups for purposes of recovery. The primary tradeoffs there are obviously complexity of management and disk space required.

  • @phoebus thanks so much for your help! which type of backup plan is easiest and fastest to recover? – l--''''''---------'''''''''''' Oct 27 '10 at 17:22
  • There is no one perfect answer to that, because it's at least in part a tradeoff between ease of recovery and granularity and scope of recovery, along with performance and disk space. For example, combining weekly full backups with daily differential backups allows you to restore fairly quickly to a particular day, without having to use the large amount of disk space a daily full backup would require. However, if you had the disk space and could afford the small performance degradation of a full backup daily, then daily fulls would be the fastest way to restore to a particular day. (cont'd) – phoebus Oct 27 '10 at 17:32
  • @phoebus i want to do transactional backups. like every 15min or so. – l--''''''---------'''''''''''' Oct 27 '10 at 17:33
  • Similarly, you could take a transaction log backup every five minutes for super-granularity of roll-forward recovery, but that will create quite a few more files and may cause a small performance hit. It's all about balancing performance, disk space, complexity, and business needs. – phoebus Oct 27 '10 at 17:33
  • I'd say start with weekly full backups (say, Saturday night), daily differentials the rest of the week, and transaction log backups every fifteen minutes. Decide how long you want to retain them, then see how it works for you. Do test restores, and figure out if the set up is giving you the coverage you need based on your business case. – phoebus Oct 27 '10 at 17:34

I'm not an expert, but my understanding is that with transaction log backups you can rollback transactions that occur. This is not necessary if you are not wanting to undo things that have happened.

The question "is a daily full backup enough?" depends on the situation. If it's every 24 hours, and you lose the database 1 hour before the backup, then you've lost 23 hours worth of data.

A differential backup backs up all changes since the last full backup, an incremental backup backs up all changes since the last full or differential/incremental/full backup. Depending on your setup, one of these might be useful for you.

You could do something like adding a differential backup in the middle of your full backup, or use incremental backups throughout the day...

  • 1
    Doing transaction log backups doesn't give you the ability to roll back - it gives you the ability to restore to a certain point in time by (more or less) "replaying" everything that the database did up until that point. – Bob Pusateri Oct 27 '10 at 17:22
  • +1 (and -1 on the answer): Exactly. Tx Logs are not there for undo... they are there for replay functionality. – TomTom Oct 27 '10 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.