Scheduling a DB restore - 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

General SQL Server Forums

New to SQL Server Programming

Scheduling a DB restore

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2013-09-27 : 12:34:36

I need to be able to restore a test db from a production db on a daily basis..

Can someone please let me know how to do that..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-27 : 12:58:30

You can schedule an agent job to restore the database. You would run a T-SQL script via the agent. Look at the examples in this page to see sample restore scripts: http://technet.microsoft.com/en-us/library/ms186858.aspx

Do you also need to generate the backup from the production database, or is that already done as part of a maintainence job?

NeilG
Aged Yak Warrior

530 Posts

Posted - 2013-09-30 : 04:08:36

I use the below from clause to build up a restore statements for a routine job I created to check every full backup taken over the weekend. Happen you could do something similar and then schedule a SQL agent job to restore the test database.

FROM
MASTER.sys.databases sd
LEFT OUTER JOIN msdb.dbo.backupset bs ON RTRIM(bs.database_name) = RTRIM(sd.name)
LEFT OUTER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE
(bs.type = 'D' AND DATEDIFF(DAY, bs.backup_finish_date,GETDATE()) < 7)
AND
(bs.is_copy_only <> 1 AND sd.name NOT IN ('TempDB','Master'))

NeilG
Aged Yak Warrior

530 Posts

Posted - 2013-09-30 : 04:20:37

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources