Perform SQL Server Backup Exercises with the GUI and T-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.
(back to top)Requirements:
- As with all of the exercises on this site, do not perform this exercise on a production server - perform this exercise in a closed development environment
- You will need to have a backup device already created - to see the Create Backup Device exercise click here
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.
(back to top)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.
T-SQL Steps:
1) To perform a backup using only T-SQL, an example is provided below with the proper syntax. This command was run within query analyzer successfully. Note that this example was actually obtained from the SQL Server Agent job for the backup that we created in the GUI steps presented above.
![T-SQL statement to do full backup - BACKUP DATABASE [Northwind] TO [productionbackups] WITH NOINIT, UNLOAD, NAME=N'Northwind Backup Complete', NOSKIP, STATS=10, NOFORMAT](/images/createbackup/sqlcb16.gif)
2) For a full explanation of the options for the Backup database statement, refer to books online.
[NORTHWIND] - database to be backed up
[productionbackups]- device to back up to
NOINIT - append existing backup set to the current backup sets on the device that is being backed up to
NOUNLOAD - Here by default, really used only when backing up to tape devices, and we are backing up to disk
NAME - Name of the backup set
NOSKIP - The expiration dates of backup sets must be checked before they can be overwritten
STATS - Displays a message to show the progress of the backup
NOFORMAT - Leave the media header as is (using format would invalidate the existing content on the media that you are writing to)
(back to top)