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.

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:

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:

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:

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