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?
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'
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:
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.