Automate/Schedule the Database Restore

Already in this Post, we have seen how to Automate/Schedule the Database Backup
Today we are going to see how to automate the database restore task.

1. Open the SQL Server Management Studio. Connect to the database server. I have connected to localhost database server.
2. Expand the “Management” Option. Under that You can able to see an option called “Maintenance Plans”
3. Right Click on the Maintenance Plan and Select “New Maintenance Plan”

4. Enter the Name for the Maintenance Plan. I have given name as “AutoRestore-AdventureWorksDB” and Give Ok.
This will navigate you to the design page of that plan.

5. Enter Description as “To Restore the Database on a daily basis”
6. For Scheduling this job, Click on the Calendar Like icon on the SubPlan Schedule Column.

7. This will open the “Job Schedule Properties” window. Select the “Frequency Occurs” as “Daily”. Leave remaining things as it is.
8. If you are restoring a database in your local machine, give the time as “12:00:00 PM” or the time you want.
When the job runs during that time, the machine/server should not be shut down/switched off.

9. Give Ok. Now Click on the Toolbox tab in the “Design” Page and Double Click “Execute T-SQL Statement Task”

10. This will create the “Execute T-SQL Statement Task” in the Design page.

11. Double Click on the “Execute T-SQL Statement Task” in the Design Page and this will open the “Execute T-SQL Statement Task” window.

12. Enter the following Query in the window and give OK.

RESTORE DATABASE [AdventureWorks2008R2]
FROM  
DISK = N'D:\SQL DB\BACKUP\AdventureWorks2008R2.bak' 
WITH  FILE = 1,  
MOVE N'AdventureWorks2008R2_Data' TO N'D:\SQL DB\DATA\AdventureWorks2008R2_Data.mdf',  
MOVE N'AdventureWorks2008R2_Log' TO N'D:\SQL DB\DATA\AdventureWorks2008R2_Log.LDF',  
NOUNLOAD,  
STATS = 10
GO

13. Save Selected Items. That’s it. You have automated the restore database task.

Optionally you can create one more “Execute T-SQL Statement Task” and you may delete the database using the following query.

USE [master]
GO
ALTER Database [AdventureWorks2008R2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AdventureWorks2008R2')
DROP DATABASE [AdventureWorks2008R2]
GO

Advertisement