The Database Design Resource Center

SQL Server Backup

SQL Server Backup:

When using SQL Server to maintain and manage a database, the integrity, accuracy and availability of that data if often critical to the success of a business.

If data becomes unavailable or the database becomes inaccessible to the users of a system, a business can be in a lot of trouble.

SQL Server has the ability to make backups of databases so that the data can be restored in case something tragic happens to the database. A SQL Server backup will play a critical part to any disaster recovery plan in case an emergency does occur.

They are crucial to the success of any information technology department. How do you perform a SQL Server backup in order to ensure that the database will be available in the event that something does happen sometime down the road and your database must be restored from a backup device?

In order to backup a SQL Server database using Enterprise Manager, right click on the database that you want to back up. The following illustration shows the menu options needed to navigate to the necessary option.

SQL Server backup

After the appropriate menu is available, from the All Tasks menu item select the Backup Database submenu item. This will initiate the SQL Server Backup Database operation so that the parameters needed to perform the backup can be entered into the database.

This will bring up the following screen to begin the entry of the information needed to perform the backup of the database:

SQL Server backup

The first option in the screen is to specify the database that is to be backed up and the name of the backup as well as a description of the backup.

A name and description are necessary in order to help identify saved SQL Server backup plans in the database. After this information is entered into the window, the backup type must be selected in order to specify what type of backup is to be performed.

There are four backup operations that can occur within the database. This can be a complete database backup, a differential database backup, a transaction log or file/filegroup backup.

A complete database backup will back the entire database structure up to the device specified. It is a complete copy of the database selected and will include everything in the database including the data and the objects.

A differential backup is another option. It deals with only backing up the database and its’ changes since the last full database backup was performed. A transaction log backup will backup the current transaction log so it can be restored at a later date.

The transaction log is a log of the transactions that have taken place in the database. A file or filegroup backup deals with making a SQL Server backup of a specific part of the files or filegroups involved with the make up of the database.

Once the desired backup type is entered, a destination for the database backup must be input into the equation. This will inform the database where the backup is to be saved. The backup can be saved to a disk or to a tape backup device.

To enter a backup device to save the backup to, click the Add button to open a dialog that will allow for the selection of the backup location and the device to hold the backup.

After finalizing the selection of the backup destination, the choice must be made whether to append to the existing media or overwrite the existing media with the result of the backup.

Once all of these options have been entered, the database backup can be scheduled in order to make this a process that occurs on a regular basis. Scheduling a backup procedure in SQL Server puts the workload on the server to execute the backup on routine basis and does not force a database administrator to perform the backups.

The only job that someone has concerning the SQL Server backups are to either routinely check them for success or configure the server to send alerts to users when specific actions occur on the server.

To schedule the backup of the database, select the checkbox and then click the command button to bring up the scheduling dialog window. The following screen shows this operation:

SQL Server backup

The options on this screen deal with the timing of the SQL Server backup and the conditions that need to be met before the server is to run the database backup.

The schedule can be given a name to help distinguish it within the database. It can also be enabled or disabled using the Enabled checkbox option. There are four different scheduling options that can be used within the system to determine when to run a job within SQL Server.

These four options are to start when agent starts, start when the CPU is idle, a one time or to create a recurring job.

The first option will start the job whenever SQL Server Agent starts. SQL Server Agent is a service that runs specific tasks within the database management system.

The second option is to start the job when the CPU becomes idle.

The third option is to create a job that occurs once at a specific date and time.

The final option is to create a SQL Server backup job that occurs on a routine basis that can be scheduled to occur as often and whenever it is necessary.

The following screen shows the job scheduling options available to handling recurring jobs:

SQL Server backup

Once the job schedule is created, clicking OK will accept the changes and create a job within the database.

This job is responsible for the backup and is run by SQL Server Agent whenever the scheduled time occurs. When the scheduled time occurs on the server, SQL Server Agent takes over and runs the job and the steps associated with it. While running the job, the status of the SQL Server backup is updated to alert the user of the database what is occurring with the job. Each step and operation is logged in the database and updated in the job history tables stored in the database.

Return to SQL Server DBA

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © /
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.