Automate/Schedule the Database Back Up

Today we are going to see how to automate the backing up a database.
If we are frequently making changes in a database on a daily basis, then it is a good practice to backup the database
before making Changes. Instead of doing this manually, we can also automate the database backup task in MS-SQL Server.
It is very easy and very simple.

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”

Image

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

Image

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

Image

7. This will open the “Job Schedule Properties” window. Select the “Frequency Occurs” as “Daily”. Leave remaining things as it is.

Image

8. If you are backing up a database in your local machine, give the time as “12:00:00 PM”. 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 “Backup Database Task”

Image

10. This will create the “Back Up Database Task” in the Design page.

Image

11. Double Click on the “Back Up Database Task” in the Design Page and this will open the “Back up Database Task” window.

Image

12. If you are backing up the database in local, then leave the connection as “Local Server Connection”
Or Click on the New button and select the Server. Leave Backup type as “FULL”
Select the Database that you want to backup. I have selected “Adventureworks2008R2” database.

Image

 

13. Enter the Directory for the Database backup. I have given as “D:\SQL DB\BACKUP”. Leave remaining settings as it is and give Ok.

Image

14. Click on “Save” or press “Ctrl+S”.

Thats it. Now you have successfully automated the backup of “AdventureWorks” database.
Now this job will run daily on “12:00:00 PM” and it will create a backup [.bak] file in “D:\SQL DB\BACKUP” directory.

 

Advertisements

One thought on “Automate/Schedule the Database Back Up

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