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?

  • 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? Jul 27, 2013 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. :) Jul 27, 2013 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, 2013 at 8:59

3 Answers 3


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:


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


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:


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%"

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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