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”

AR1

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.

AR2

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.

AR3

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.

AR4

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

AR5

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

AR6

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.

AR7

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s