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?
SQL Server – Restore Database Maintenance Plan Task
maintenance-plansrestoresql server
Related Solutions
You need to change the appropriate upstream precedence constraint(s) in the flow to On Completion instead of On Success.
This is done one of two ways:
- Right-clicking the constraint on the design surface (shown below) and selecting the appropriate type from the popup menu.
- In the Precedence Constraint Editor dialog (which you had open), select the appropriate type from the Value dropdown list.
In earlier versions than 2012, the constraint arrow will turn blue; in 2012, the arrow gets a more explicit "Completion" caption.
Now having said that, if you want to keep all of this in one flow, I would actually recommend using SQL Server Data Tools to create the package, because what you really need is a Sequence Container to put all the maintenance stuff in (keeping the existing On Success constraint types), and then connect the container to the backups task with an On Completion precedence constraint.
Alternatively -- and this is what I would usually do -- separate the backup portion of the process into a different subplan, and schedule it after the maintenance portion is expected to complete.
Completion is a task state that is true if:
a. The task state is Success.
b. The task state is Failure.
A task will never reach any of these states until a TaskStart event has caused the task to start executing. A disabled task, or a task that is never executed, will not reach a state of Completion.
Related Question
- SQL Server – Maintenance Plan Job Running at Unscheduled Times
- Sql-server – How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL
- SQL Server Backup – Should Multiple Maintenance Plans Be Created for Over 200 Databases?
- SQL Server – Choosing Specific Database Name in T-SQL Statement Task
- SQL Server – Transaction Log Backup Maintenance Plan for Restored Databases
- Creating a Maintenance Plan for a SQL Azure Database
- SQL Server – Full Database Backup Without a Job
Best Answer
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.