Restore Database and Logs - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Restore Database and Logs

Author

Topic

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2009-09-15 : 15:32:19

Hi,

We are running SQL 2005 Ent as our primary server and we log ship to a secondary backup server. The logs are shipped every 15 minutes.

We also perform a full database dump every night.

Question. If I want to restore the database to a developmemt server (not the backup server), and if I want to restore the lasest full dump AND the logs up until a specific time, how can I do that. On the DEV server I select the full database dump to restore but I do not see any log files.

I remember that prior to log shipping I could select the dump to restore and all log files would appear and I could select the ones I wanted to restore.

Thanks,
Terry

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-15 : 18:04:27

You can do it manually, no need to use the GUI.

First restore the DB backup with the NORECOVERY option
Then restore all the logs, in sequence, all with the NORECOVERY option, starting with the first one after the full backup, ending with the final log that you need
If you're 100% sure that all necessary logs have been restored, recover the DB.

So...

RESTORE DATABASE SomeDB FROM DISK = 'D:\SomeDBFullBackup.bak' WITH NORECOVERY
RESTORE LOG SomeDB FROM DISK = 'D:\SomeDBFirstLogBackup.trn' WITH NORECOVERY
... restore all log backups in sequence
RESTORE LOG SomeDB FROM DISK = 'D:\SomeDBLastLogBackup.trn' WITH NORECOVERY
-- Check that all logs that need restoring have been restored.
RESTORE DATABASE SomeDB WITH RECOVERY -- recover the DB and bring it online.

If you want to restore to an exact time, look up the STOPAT option for restores in SQL's Books Online.

--
Gail Shaw
SQL Server MVP

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2009-09-15 : 19:02:19

Thanks Gail.

If there are 20 logs is there any way to restore all 20 in a single command? I actually have 7 databases that have logs created every 15 minutes so one at a time would be painful.

Terry

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-15 : 19:58:04

Each tlog backup must be restored with a RESTORE LOG command. They can not be combined into one command.

You can easily script out the commands though. Let us know if you need help with that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2009-09-16 : 20:48:41

Hi Tara,

When you say "script out" the commands ... Do you mean that a script could be created that would look for and restore the first log and then look for and restore the second log, etc?

Terry

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-09-16 : 23:23:42

You need to setup like this:

http://www.mssqltips.com/tip.asp?tip=1584

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2009-09-17 : 06:37:22

That's Perfect! Thank you!

Thanks,
Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources