how to Schedule Backup in SQL

 Description:

There are three major types of backups that we explore on this site: Database Backups, Differential Backups, and Transaction Log backups.  In this exercise we are performing a database backup.  We're also putting this database backup on a recurring schedule.

Setup:

We schedule a job in this exercise, so make sure that the SQL Server Agent is running in order to successfully complete this exercise.

GUI Steps:

1) Right click on “Backup” from the management folder and choose “Backup a Database”

2) The SQL Server Backup screen comes up. Choose the Northwind Database and provide a name for your backup. Choose "Database - complete" from the Backup section. You must then choose a destination for your backup to go to. Click the 'Add' button to let us choose from backup devices.

3) The "Select Backup Destination" screen appears. Click on the Backup device radio button and choose a backup device. We're choosing the productionbackups device that we created in the "Create Backup Device" exercise. Click 'OK' to return to the SQL Server Backup Menu.

4) We're going to put our backup on a schedule. Left click on the Schedule box, and then click on the ellipses button to open up the scheduling options.

5) The Edit Schedule screen appears. Provide a name for this backup schedule. We're going to do this complete database backup on a recurring schedule. Click on the 'Recurring' radio button, then click on the 'Change' button.

6) We're going to set up a daily job that runs at 10:15AM. You'll want to perform your complete database backups when there is little to no activity on the database, probably sometime in the wee hours of the morning. We chose daily, occurs at 10:15AM, along with a start date and no end date. Then left click on 'OK'.

7) You are now returned to the edit schedule screen which shows in the recurring box that our daily backup has been accepted. Click on 'OK'.

8) You click ok and are returned to the SQL Server Backup Screen. Note that all of our work so far has been accepted, including our recurring schedule that we just added. Click on the options tab so we can verify our backups.

9) You are now on the Options tab. Click on verify backup upon completion. This will check the integrity of the backup. The other options shown as clicked below are on by default. Remove inactive entries from the transaction log will free up space in the transaction log by removing transactions that have already completed. Clicking on OK from here will return us to enterprise manager.

10) Now, if we go to SQL Server Agent, and click on "Jobs" we'll see that we have a recurring backup job that has been created.

11) After the job is scheduled to be performed, we can go to the device that should contain our backup. Double click on the 'Backup' icon in the management folder. The backup devices appear in the right hand window. In our example, we put our complete backup in the production backups folder. Double click on the production backups folder to see if our backup was successfully completed.

12) We are taken to the 'Backup Device Properties' screen. Click on the "View Contents" button to see if our backup is present.

13) The view backup media contents shows two backups, one of which is the Northwind Backup Complete that we set up in this exercise (it is highlighted). Note that the date and time were the current date at 10:15 as we specified. You can click on close to leave this screen and return to the Backup Device Properties screen (from the previous step). From the Backup Device Properties Screen you can click on 'OK' to return to Enterprise Manager.