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?
gopher_49Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbbishopCommented:
That is correct!
0
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.
0
DcpKingCommented:
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.

hth

Mike
Screenshot-from-2013-10-07-21-43.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.