Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to schedule a task that restores a database once a week from a backup. I have setup maintenance plans for backing up databases (Management > Maintenance Plans) but cannot find out how to make a restore task - does this need to be an Execute T-SQL Statement Task, or is there a Toolbox item for it?

share|improve this question

migrated from serverfault.com Jul 27 '13 at 12:55

This question came from our site for professional system and network administrators.

1  
What is the purpose behind this? Is this for a "cold backup" that needs to be up to date? Or a test machine that you want to restore from the same backup each time? –  Kenneth Fisher Jul 27 '13 at 15:05
1  
Oh, interesting. Stanley seems to be thinking along the lines of regular restores for a training environment, Jon seems to be thinking along the lines of DR, and I'm thinking along the lines of dev/stage refresh. I'm curious to see which one of us guessed your business requirements correctly. :) –  Katherine Villyard Jul 27 '13 at 16:30
    
There is a production database which will be restored into a test environment each week. I also have a Logging database which only has 5 tables but grows at 10-15GB/week. The logging database is being archived away weekly and then restored over from an empty database. Thanks for the answers! –  sa555 Jul 29 '13 at 8:59
add comment

3 Answers

up vote 3 down vote accepted

There is no 'Restore DB Task' in the maintenance plan. You will have to use the Execute T-SQL Statement Task. In that task, add a restore script in your T-SQL statement. Simple example:

RESTORE DATABASE YourDBName FROM DISK = 'D:\DBbackups\YourDB.BAK'
WITH REPLACE
GO

Then create a schedule for weekly execution. Hope this is what the business requires, as you are overwriting a DB.

share|improve this answer
add comment

There isn't anything built into the Maintenance Plans that does this.

However, Transaction Log Shipping is built-in/supported and provides a way to keep a copy of a database in two places. It was built with disaster recovery in mind, but there's no reason why you couldn't leverage that for your purposes (aside from the requirements and limitations inherent with using Log Shipping to start with). It might even be more efficient than doing a full restore every week.

If Log Shipping is unsuitable, you'll either need to find someone else's solution, or roll your own. You could also look at built-in solutions like Replication (Snapshot Replication in particular), but that may be overly complicated for your needs.

share|improve this answer
add comment

Like Jon said, there's nothing in the maintenance plans that does this.

I'm assuming that you're looking to copy the current state of a database for staging or development environments, not looking for something like log shipping. If so, you could do something like this in task scheduler with batch and t-sql:

ECHO ON

set backupscript=c:\path\backup.sql
set restorescript=c:\path\restore.sql

set backupstore=\\server\share
set restorestore=\\server\share

set backupserver=server\inst
set restoreserver=server\inst


sqlcmd -E -S%backupserver% -i "%backupscript%"
copy "%backupstore%\*" %restorestore%
sqlcmd -E -S%restoreserver% -i "%restorescript%"
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.