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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Basic Backup/Restore Question

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 hour

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

Now it seems the log backups actually span the differential, that is, I have date-times like this:

log 5/1/2015 2:00 AM
dif 5/1/2015 2:30 AM
log 5/1/2015 3:00 AM

So 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,
Ken


kpg

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
Go to Top of Page

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
Go to Top of Page

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 disaster

quote:
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 CHECKDB

on 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
Go to Top of Page

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...

Thanks

kpg
Go to Top of Page

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
Go to Top of Page

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.html
You'll need to use the NO_TRUNCATE flag

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -