Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kpgraci
Yak Posting Veteran
68 Posts |
 Posted - 2015-05-05 : 15:15:21
|
| I've been backing up my sql db for a while now but I have never had to restore it - so I want to make sure what i'm doing is correct and if there if there's a better way.I do this: A full backup every week A Differential backup every day A Transaction log backup every hourI believe that if I need to restore I would: Restore the latest full backup Restore the latest differential backup Restore each transaction log dated after the latest differentialNow it seems the log backups actually span the differential, that is, I have date-times like this:log 5/1/2015 2:00 AMdif 5/1/2015 2:30 AMlog 5/1/2015 3:00 AMSo in this case do I restore the diff then log 2AM, 3AM, etc, or just dif then log 3AM?Also any suggestions to improve this scheme welcome. I'm happy with one hour between backups.Thanks,Kenkpg |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-05-05 : 16:50:12
|
Since the Diff is at 2:30, you would use the first log after that time, which would be the 3am log. Then you restore any other log files that you'd want (4am, 5am...)Your overall backup approach seems fine and fairly common. Others will use different frequencies for the various types of backups but it depends on your tolerance for data loss and how long you can afford to be offline in the event of a disaster. The item I do see missing is you need to test out your ability to restore the backups, typically to a test server. No one cares if you have the perfect backup plan; what they care about is the perfect restoration plan. If your backups aren't restoreable...(you see where this leads).
I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-05-06 : 01:19:50
|
| All commited transactions during the DIFFERENTIAL backup (and FULL backup) are included as part of that backup. Therefore you only need to restore transaction logs after the DIFFERENTIAL.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-06 : 04:38:31
|
quote:
Originally posted by kpgraci
A Transaction log backup every hour
I recommend changing that to every 10 minutes. The total size of LOG backups in a day is the more-or-less the same, whether hourly or every 10 minutes (except for some small overhead on the file), and more frequent gives you more chance of smaller data loss after a disasterquote:
I believe that if I need to restore I would: Restore the latest full backup Restore the latest differential backup Restore each transaction log dated after the latest differential
"latest" is ideal. However, if you find that the FULL or DIFFFERENTIAL is corrupted you can restore an earlier FULL (and an earlier DIFFERENTIAL [made BEFORE the next FULL backup] and then ALL Log backups after that, in chronological order. If you have to restore an "older" FULL backup then you will have to restore many more LOG Backup files, but it does give you more chances of getting a clean restored database.After restore run:DBCC CHECKDBon the database to check that it is not [physically] corrupted. As others have said you can restore to a test server (or if you don't have one to the same server but as a DIFFERENT  (temporary) database name, and then run DBCC CHECKDB on that to test that it is OK. (There are various options to DBCC CHECKDEB that take longer, but perform more thorough checks)One thing to note: If you have a disaster which corrupts the database (but does not destroy the server's disks!) then you may be able to make a TAIL LOG BACKUP. If you can then when you restore you will have ZERO data loss - which is a good time to ask for a pay rise |
|
|
|
kpgraci
Yak Posting Veteran
68 Posts |
Posted - 2015-05-06 : 04:41:01
|
| Great! I seem to recall doing a test restore when I first setup the backup plan and I also remember some gotchas in the process, but that's all I remember. I suppose a good idea would be to do it again and write everything down this time...Thankskpg |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-06 : 04:51:19
|
Worth doing the test restore, say, once a month. I would include restoring the file from Tape. Tape drivers change, operating system is patched/upgraded, things become incompatible ...... worst case I ever came across was the stepper motor for the tape head was bust. It recorded everything on Track 1 ... perhaps it was supposed to be 8 tracks? So only the last 1/8th of the backup was restorable  |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-05-07 : 01:37:15
|
| @kpgraci - document the restore process and attempt to have someone else test the steps from documentation. following on from comment by Kristen - if you need to take a TAIL LOG backup and the data file is corrupted - it is possible to take a tail log backup - check here for details - http://www.sqlserver-dba.com/2015/04/backing-up-the-tail-log.htmlYou'll need to use the NO_TRUNCATE flagJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
Basic Backup/Restore Question