September 7, 2005 at 8:41 am
I am about to put a new application live and the owners have requested a Full Backup and Transaction Log backups every 15 minutes.
During testing of the backup process I have come to the consclusion that you cannot run a transaction log backup at the same time as a full backup (apologies if that is an obvious statement).
Owners of the application are concerned about what happens if problems occur with database during the backup because the transaction log backups have not been running.
What happens to transactions when the database is being backed? Are they committed directly to the DB so would be part of the full backup that is running?
Any help/comments would be much appreciated.
Regards
Pete Humble
September 7, 2005 at 9:35 am
SQL Server 2000 - During a full backup, all committed transaction are included in the backup as of the conclusion of the backup. Example:
7:00pm - Full backup Starts
7:14pm - insert 10,000 rows.
7:15pm - backup completes.
The resulting full backup will include all transactions through 7:15 (including the 10,000 rwo insert).
SQL Server 7 The above is not true. The full backup would only include transactions up until 7:00pm. The 10,000 row insert would not be in the backup file.
You don't need both a full backup every 15 minutes and a transaction log backup every 15 minutes to recover the database (just one FULL and the subsequent T-;pg backups). You may want to look into Differential backups as well, just to keep the number of backup files needed for a recovery smaller.
I'll try to find some supporting info in Books Online and post it.
September 7, 2005 at 9:42 am
There's a lot of information about this subject in Books Online. Check out 'Backing up databases' and 'Backup and Restore Operations.'
Unfortunately, Books Online isn't the best resource for learning about this stuff in the first place (It's an excellent reference). You'll be reading fifty different articles and getting some seemingly contradictory information. Your best bet may be to search Microsoft.com from some white papers on recovery (http://www.microsoft.com/sql/techinfo/default.mspx) or looking for a book on the subject (there are many to choose from). There are also probably some great articles on this site related to backups and Recovery.
More info:
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/operations.mspx
September 7, 2005 at 9:51 am
Thanks for the information.
This does make sense based on the testing that I have done.
Just to confirm, I am not doing a Full Backup every 15 minutes, I just couldn't type properly. Full Backup once a day (early morning), transaction log backup every 15 minutes.
An article I read earlier also suggested running differential backups. Is this something worth doing?
Regards
Pete
September 7, 2005 at 9:59 am
you should be fine then. The full will include everything up to the completion of the backup.
The Differentials just help keep you from having to restore ALL of those transaction logs. here's an example:
12:00am - Full Backup.
12:15am - 3:45am - T-log backups every 15 min.
4:00am Differential Backup
4:15am - 7:45am - T-log backups every 15 min.
8:00am Differential Backup
8:15am - T-log backup
8:19am - Database becomes corrupt
Without getting into the point-in-time restore stuff, here's how you would recover your database:
Restore 12:00am FULL (WITH RECOVERY)
Restore 8:00am Differential (WITH RECOVERY)
Restore 8:15am T-log (WITH RECOVERY)
Without the diferential backups, you would be restoring each and every t-log backup after the FULL restore.
(Differentail backups backup all changed data since the last FULL backup, that's why you can skip all of those t-log backups and the 4:00am Differential backup as well).
Hope the above makes sense.
September 7, 2005 at 10:02 am
BTW. In this case Diff backups also help keep you from having one of those 15 minute t-log files be corrupt and messing up your whole recovery. I'd hate to start a long restore process at 8:00pm only to find out that the T-log backup from 3:45am is bad and you can't recover beyond that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply