SQL Server backup and restore, part I: The basics

Introduction

One of the most important aspects for a database environment is ensuring reliable backups are being executed and a dependable recovery plan is established in the event of a system failure or data corruption. Several options are available for defining your backup and recovery model and your choices will determine the reliability and the amount of data loss your company can acceptably incur.

This document analyzes the various options available for your backup and recovery process with SQL Server 2000. The next part of the series will present best practices and tips.

Recovery Model

Which recovery model is best for the databases in your environment? This setting depends on the critically of the data and the acceptable data loss in the event of a system failure. SQL Server 2000 offers three recovery models that can be implemented for your databases. The appropriate choice depends on your applications and the criticality of your data. These settings can be configured either through Enterprise Manager or through T-SQL using the ALTER DATABASE command.

The three database recovery model options are:

1. Simple - With the Simple Recovery model, data is recoverable only to the most recent full database or differential backup. Transaction log (or incremental changes) backups are not available. The Simple Recovery model is easier to manage than the Full or Bulk-Logged models, but at the expense

    Requires Free Membership to View

    Login

of higher data loss because the contents of the database transaction log are truncated each time a checkpoint is issued for the database.

2. Full - The Full Recovery model uses database backups and transaction log backups to provide complete protection against failure. If one or more data files are damaged, restoring the backups permits recovery of all committed transactions using a combination of the database and transaction log backups. Full Recovery provides the ability to recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged and recoverable.

3. Bulk Logged - The Bulk-Logged Recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, Bulk load operations, CREATE INDEX as well as text and image operations. Under the Bulk-Logged Recovery model, a damaged data file can result in having to redo work manually based on the operations above that are not fully logged. In addition, the Bulk-Logged Recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes. Point-in-time recovery is not supported.

SQL Server 2000 Enterprise Manager directions to configure the database Recovery Model: In SQL Server Enterprise Manager, open the 'Databases' folder. Once the database folder is expanded, right click on the database and select the 'Properties' option. The 'Database Properties' window will open. Click on the 'Options' tab and the recovery model will be listed in the middle of the screen. Click on the drop down box to select the needed recovery model. On the bottom of the screen click 'OK' to save the Recovery Model.

SQL Server 2000 Transact-SQL directions for ALTER DATABASE commands to configure the database Recovery Model:

 ALTER DATABASE Northwind SET RECOVERY FULL GO

In this example the Northwind database is set to "Full" Recovery Model.

Backup Options

Once the database recovery model has been identified, it is necessary to decide which backup method needs to be instituted for your backup and recovery procedures. There are several options and each has advantages and disadvantages. The backup options can be configured with either the Maintenance Plan Wizard, Enterprise Manager or through the use of T-SQL commands. Below outlines the available backup options:

Database
This option creates a full copy of the database. A complete snapshot of your database is created at the time the backup occurs.

Transaction
This option provides a copy of the active transaction log. Transaction log backups operate in conjunction with database backups to allow you to append transactions that have occurred since the last database backup. If successive logs are created, each log creates a set of the new transactions since the last transaction log backup.

Differential
This option copies only the database pages which have been modified after the last database backup. If successive differential backups are created, only the most recent differential backup is required for the recovery process. Differential backups are leveraged in combination with full backups. It is necessary to execute a full backup first and then execute the Differential backups on the needed interval. In addition, it is possible to use transaction log backups with differential backups based on the backup schedule.

File or Filegroup
For very large databases, an option is available for executing database file or filegroup backups. These backups allow you to backup a single data file at a time. One of the drawbacks with this option is that it requires more effort in planning the backup and recovery process as well as your overall database design. In most instances you only have one data file and one log file for each database and therefore this option does not make sense. Also, in order to use filegroup backups you must use transaction log backups in conjunction with this backup method.

Snapshot Backups
Using third party tools, such as Storage Area Network (SAN) solutions, you have the ability to capture file level snapshots of the database to replicate the complete database files to other disk drives on the SAN. Unfortunately, this method is expensive and not an option for most database installations.

Backup Commands

There are primarily two options when constructing backup commands, either backing up the database or the transaction log. In conjunction with these commands, there are several options which can be specified when constructing your backup commands. These additional options can be found in SQL Server Books Online in an article entitled 'BACKUP'.

In the commands below, the {device} reference can specify either a logical or physical device. In constructing the commands you can reference the name of a physical file or you can specify a logical device that has been setup through Enterprise Manager or T-SQL. More information about this can be found in SQL Server Books Online.

DATABASE
This option specifies backing up the data portion of the database. For this command there are options to specify the full database, a list of files/filegroups or differential backups. The backup commands are constructed as follows:

Database

 BACKUP DATABASE {databasename} TO {device}.

Differential

 BACKUP DATABASE {databasename} TO {device}.WITH DIFFERENTIAL

Filegroup

 BACKUP DATABASE {databasename} FILE = {filename}, FILEGROUP = {filegroup} TO {device}

LOG
This option specifies a backup of the active transaction log. The log is backed up from the last successfully executed LOG backup to the end of the log. The command is constructed as follows:

 BACKUP LOG {databasename} TO {device}.

Tracking Tables
Several tables exist in the msdb database that track the backup operations which occurred on the server. These tables include:

  • backupfile - Contains one row for each data or log file that is backed up
  • backupmediafamily - Contains one row for each media family
  • backupmediaset - Contains one row for each backup media set
  • backupset - Contains a row for each backup set

Restore Commands

The restore commands are equivalent to the backup commands in terms of syntax. You have the option to execute database or transaction log restores. In addition, there are more commands available that permit checking the validity of the backup file as well as read the contents of the backup file prior to executing a restore.

DATABASE
Specifies the complete restore of the database from a backup device. This can either be a full database, differential or a filegroup restoration. If a list of files and filegroups is specified, only those files and filegroups are restored.

Database

 RESTORE DATABASE {databasename} FROM {device}

Database and Differential

 RESTORE DATABASE {databasename} FROM {device} WITH NORECOVERY RESTORE DATABASE {databasename} FROM {device}

Filegroup

 RESTORE DATABASE {databasename} FILE = {filename}, FILEGROUP = {filegroup} FROM {device} WITH NORECOVERY RESTORE LOG {databasename} FROM {device}

LOG
Specifies a transaction log restore is to be applied to the database. Transaction logs must be applied in sequential order from the oldest backup to the most recent backup. SQL Server checks the backed up transaction log to ensure that the transactions are being loaded in the correct database and in the correct sequence. To apply multiple transaction logs, use the NORECOVERY option on all restore operations except the last restore command where the database recovery is needed. In addition, a transaction log restore must be executed following the database restore.

 RESTORE DATABASE {databasename} FROM {device} WITH NORECOVERY RESTORE LOG {databasename} FROM {device} WITH NORECOVERY RESTORE LOG {databasename} FROM {device}

VERFIYONLY
Verifies the validity of the backup, but does not restore the backup. This process confirms that the backup set is complete and that all volumes are readable for SQL Server to restore the backup in the future. However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. If the backup is valid, the following message is returned: "The backup set is valid."

 RESTORE VERIFYONLY FROM {device}

FILELISTONLY
Returns a result set with a list of the database and log files contained in the backup set.

 RESTORE FILELISTONLY FROM {device}

HEADERONLY
Retrieves the backup header information for all backup sets on a particular backup device.

 RESTORE HEADERONLY FROM {device}

Tracking Tables
Several tables in the msdb database house all of the restore operations that occurred on the server. These tables are as follows:

  • restorefile - Contains one row for each restored file, including files restored indirectly by filegroup name
  • restorefilegroup - Contains one row for each restored filegroup
  • restorehistory - Contains one row for each restore operation

[Part II of this article can be found here.]

 

About the Author

Greg Robidoux is the founder of Edgewood Solutions, a database solutions company in the United States, and is currently the Vice Chair of the PASS DBA Special Interest Group. He has 14 years of IT experience and has been working with databases for the last 10 years with the past four years of that with SQL Server. Greg's primary areas of focus are setting standards, disaster recovery, security and change management controls. In addition to these areas he has experience with replication, storage areas networks and SQL Server upgrades. Greg can be reached at [email protected].

 

More information from SearchSQLServer.com

 

This was first published in February 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top