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”
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.
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.
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 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”
10. This will create the “Back Up Database Task” in the Design page.
11. Double Click on the “Back Up Database Task” in the Design Page and this will open the “Back up Database Task” window.
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.
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.
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.