• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

restoring SQL DBs

I have a full database backup from 9/27/13.  The full database backup from 10/4/13 didn't complete, but, I have LOG files from the 28th to last night.  Shouldn't I simply restore the full database backup from 9/27/13 and then restore each LOG file one by one until last night?  I assume this is done via All tasks>restore>DATA and the each LOG, correct?
2 Solutions
That is correct!
Kent DyerIT Security Analyst SeniorCommented:
WAIT!  IS THERE A PROBLEM?  I did not read there was a problem other than the backup was having issue.  If there is a problem with the database - then I agree, you need to proceed.  Is the current database usable and is working?
Be careful with writing over your current database!

If the database is running and you are having no issue, then no.  Do not restore over the top of the current database.  You may have a bad backup, but you don't want to over-write your database as that is the "holy grail" of data.

Now, if you are having trouble with the database - integrity, can't read, etc.  Then we can talk about restoring data.

Hope this makes sense.
Yes, you're right. Restore the database first, and be careful to restore it "with norecovery". This sounds a little backwards, but what you're doing is telling SQL Server not to make any assumptions about open transactions because you'll be giving it some more info. The switch to use for this is on the Options tab of the Restore Database wizard, and I've attached a screenshot showing which radio button to choose.

After you're restored the database restore each transaction log that you have, starting with the oldest one since the backup, and in each case also do the restore with norecovery until you get to the most recent backup file.

Microsoft's Best Practice Tip (from their 2012 Books Online) is "to restore all the log backups (RESTORE LOG database_name WITH NORECOVERY). Then, after restoring the last log backup, recover the database in a separate operation (RESTORE DATABASE database_name WITH RECOVERY)."

This last command tells SQL Server to check all the open transactions, etc., and make it ready for use.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now