Monday, April 15, 2013

how to Schedule Backup in SQL


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.


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”
Screen Shot of Enterprise manager - Under Management Folder, Right Clicking on "Backup" and choosing "New Backup Device"
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.
Screen shot of "Backup" menu - Choosing a database name, a name for backup, and a complete backup type.  Click "Add" button to add a destination for backup device.
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.
Screen Shot of backup destination screen - choose a destination (a backup device) and click the "OK" button
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.
Screen Shot of Backup Menu - showing that our backup destination has been selected - click on Ellipses button to go on to next step.
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.
Screen Shot of edit schedule screen - provided a name for the schedule, clicking the "Recurring" radio button and then 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'.
Screen shot of Recurring Job Schedule screen - Entered a recurring job schedule and clicking the "OK" button
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'.
Screen Shot of edit schedule screen with the recurring schedule data - clicking "OK" button
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.
Screen shot of SQL Server Backup screen with recurring schedule information.  Clicking on options tab to verify the backup.
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.
Screen shot of options tab, we're clicking the Verify backup upon completion check box, then the OK button
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.
Screen shot of jobs in SQL Server Agent showing our recurring job
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.
Screen shot of us double clicking on the productionbackups device
12) We are taken to the 'Backup Device Properties' screen. Click on the "View Contents" button to see if our backup is present.
Screen shot of us clicking the "View Contents" button for the production backups device
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.

No comments:

Post a Comment