1

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?

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

This question came from our site for 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
6

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.

3

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.

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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