SQL Server 2000 Backup and Restore
Note: For information on Backup and Restore in SQL Server 2005, see Backing Up and Restoring Databases in SQL Server in SQL Server 2005 Books Online.
Author: Pankaj Agarwal
Published: February, 2005
Summary: This paper discusses the types of backups that are available in SQL Server 2000 and how backup functionality can be used in a disaster recovery plan. It includes general recommendations on how to improve backup and restore throughput and several case scenarios.
On This Page
Introduction
The need to back up databases on a regular basis is a major component of managing any production system.
Backups may be used to provide a means of recovery from a disaster situation. Microsoft® SQL Server™ 2000 provides several kinds of backups that may be combined to formulate a customized disaster recovery plan depending on the nature of the data and the disaster recovery requirements.
SQL Server 2000 enhances some aspects of the backup and restore functionality that was provided in SQL Server 7. There is also additional functionality that helps individual organizations take full advantage of commands in SQL Server 2000.
It is highly recommended that all SQL Server databases be backed up periodically. This provides the best chance of successfully recovering a production environment in the quickest amount of time in case there is a disaster situation.
This paper discusses the various kinds of backups that are available in SQL Server 2000 and how this functionality may be used in a disaster recovery plan. The paper also discusses some general recommendations on how to improve backup and restore throughput. Finally, we examine two case studies where we implement the knowledge from previous sections in real-world scenarios.
Common Terms
Data page
An SQL Server database’s basic data storage structure is 8 KB and is known as a data page. An SQL database may contain thousands of pages.
Disaster recovery planning
The process of formulating, documenting, and testing the procedures that would be performed if production data in one or more SQL Server databases were to be lost or modified in an unforeseen disaster or malicious attack.
Minimally logged operations (bulk load operations)
Data movement operations that require minimal logging in the transaction log. These operations include bcp, certain Data Transformation Services (DTS) operations, and SELECT INTO. Depending on the recovery model for a database, any of these operations might either be fully logged or minimally logged.
Filegroup
A logical grouping of SQL Server database files. By default, a new SQL Server database contains the Primary filegroup.
Log sequence number (LSN)
The unique number that each operation is stamped with when it is written to the transaction log. A single SQL Server transaction may contain several LSNs.
Logical file names
The names that are used by SQL Server to identify files within an SQL Server database.
Physical file names
The name used by the operating system to identify specific files. All SQL Server database files have both a physical and a logical file name.
Extent
A collection of 8 data pages. Since a data page is 8 KB, an extent is 64 KB.
SQL Query Analyzer
A graphical tool provided with SQL Server client utilities to query SQL Server databases using the Transact-SQL commands.
Transaction
A set of modifications that are performed as a single unit of work. A transaction follows the ACID guidelines. For more information on the ACID standard, see “Transactions” in SQL Server Books Online.
Transaction log
A record of modifications performed to a database. The amount of information logged in the transaction log depends on the recovery model for a database. For more information on recovery models, see “Recovery Models” in the next section of this paper.
Tail of transaction log
The transactions that have been committed but not backed up since the previous complete or differential database backup or transaction log backup.
Transaction undo file
File containing information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed. A transaction undo file is required if a database is loaded in read-only state. In this state, further transaction log backups may be applied.
Virtual log file (VLF)
A logical section within an SQL Server database’s transaction log. When performing a truncate of the transaction log, an entire VLF is cleaned out.
New Backup and Restore Features in SQL Server 2000
Recovery Models
SQL Server 2000 introduces the concept of recovery models for databases. Recovery models are designed to simplify the administration of SQL Server 2000 databases. There are three recovery models in SQL Server 2000—Full, Bulk-Logged, and Simple. System databases (including master, MSDB, and tempdb) are set to the Simple Recovery model. All user databases, by default, are created with the Full Recovery model (it should be noted that the Full Recovery model takes affect once a complete database backup is performed). The recovery model may be changed once the database is created.
The recovery model for a database incorporates the two most often used settings—Truncate Log on Checkpoint and Select Into/Bulkcopy.
Truncate Log on Checkpoint. In previous versions of SQL Server, this setting was selected to automatically truncate the transaction log every time CHECKPOINT is activated for the database.
Select Into/Bulkcopy. This setting was used in previous versions of SQL Server to perform non-logged operations.
Following are the settings and their relation to the three recovery models.
| Recovery Model | Select Into / BulkCopy | Truncate Log on Checkpoint |
Full | False | False |
Bulk-Logged | True | False |
Simple | True/False | True |
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making the transaction log virtually maintenance free. There are limitations placed on the recoverability of a database if this recovery model is used.
Bulk-Logged Recovery Model
A database in this recovery model will have minimum logging for bulk import operations. Space allocation and deallocation is only logged for bulk import operations. Point-in-time and point-of-failure recovery may be possible when a database is in Bulk-Logged Recovery model.
Full Recovery Model
SQL Server performs full transaction logging for any bulk load operations if a database is in Full Recovery model. Transaction log backups should be performed at regular intervals for maximum recoverability. This model provides the safest mode of operation for production systems.
The following table summarizes the recovery models and backup types available with each recovery model.
| Recovery Model/ Backup | Complete | Differential | Transaction Log | File / Filegroup |
Simple | Required | Allowed | Not Allowed | Not Allowed |
Bulk-Logged | Required | Allowed | Required | Allowed |
Full | Required | Allowed | Required | Allowed |
Partial Database Restore
New functionality in SQL Server 2000 provides commands to restore a database backup partially. If a database contains several filegroups, a single filegroup may be recovered using this new functionality. Partial database restore operations provide a means to restore only certain parts of the database, as needed.
Enhancements to Differential Backups
Differential backups have been enhanced in SQL Server 2000. A bitmap of modified extents has been added to the database structure. This bitmap contains a bit for each extent that has been modified since the previous complete backup. This bitmap is referenced when a differential backup is initiated and only modified extents are referenced and backed up. This significantly improves the performance of differential backups in SQL Server 2000.
Password-Protected Backup Files
New functionality has been added to backup functionality to password-protect the backup files. This is in line with the security initiative and helps protect against unauthorized access.
Named Log Marks
SQL Server 2000 introduces the concept of named transactions. Named log marks allow a transaction log backup to be restored up to a particular named transaction. This further enhances the point-in-time restore functionality on the transaction log backups.
Continuous Log Chain
SQL Server 2000 improves the transaction log backups. It now includes database file management functionality such as the addition and removal of database file(s) as logged operations. This improves the manageability of databases in a log shipping environment.
Recommended Reading
See the following SQL Server Books Online topics:
Additional Backup and Restore Information
SQL Server provides functionality to back up and restore SQL Server databases to disk or tape medium.
Types of Backups
SQL Server provides several different kinds of backups. A combination of these backups may be used to formulate a robust disaster recovery strategy.
| Backup Type | Description |
Complete | Backs up the entire database. |
Differential | Backs up only modified extents since the previous complete backup. |
Transaction Log | Backs up the active portion and truncates the inactive portion of the transaction log. |
File / Filegroup | Backs up individual files and filegroups within a database. |
File differential | Combines differential backups and file or filegroup backups. |
Backup Media
SQL Server databases may be backed up to either a disk or tape media. Backup may be performed through SQL Server Enterprise Manager or a Transact-SQL command.
Disk Backups
A database may be backed up to disk file or a disk backup device.
Any database can be backed up to a random disk file at any time. The file may either be initialized or the backup may be appended to an existing backup file.
Tape Backups
A database may be backed up to a local tape drive. SQL Server formats the tape backups using Microsoft Tape Format (MTF). This means that a tape may hold other backups formatted using MTF in conjunction with SQL Server backups.
Tape backups provide certain features that are not available when using disk backups.
Continuation media
If the tape to which the backup is being written fills up, SQL Server Enterprise Manager pops a dialog box and prompts for the next tape (if using the Transact-SQL command, a message is logged to the SQL Server error log to mount the next tape and a retry attempt is made roughly every five minutes to see if a new tape was mounted). This is in contrast to disk backups, where inadequate disk space terminates the backup operation.
Restart option
If there is a power failure or the server shuts down unexpectedly while the backup/restore is being performed, the operation may be restarted from the point at which it was interrupted.
Backup Devices
A backup device may be created through SQL Enterprise Manager or by using Transact-SQL commands.
To create the device through Enterprise Manager
1. | Open Enterprise Manager and connect to the server where the backup device needs to be created. |
2. | Expand the Management folder and right-click Backup to display the shortcut menu shown in Figure 1.  Figure 1: Backup Device through SQL Enterprise Manager |
3. | Select New Backup Device... to display the dialog box in Figure 2.  Figure 2: New Backup Device Properties Enter a name and location for the backup device. Notice that the Tape option is disabled in the dialog box shown in Figure 2. This is because the computer used in the example does not have a tape drive installed. |
4. | Click OK when done. This will create the backup device. Once the backup device has been created, it appears under the Management -> Backup tree within SQL Enterprise Manager. |
5. | To view the properties for the backup device, right-click the device and select Properties. The dialog box shown in Figure 3 appears.  Figure 3: Backup Device Properties |
6. | If you want to view all backups that have been performed to this device, click View Contents. |
A backup device may also be created by using a Transact-SQL command through SQL Query Analyzer. Figure 4 illustrates the use of a Transact-SQL command to create a backup device similar to that shown in the previous steps.
Adding Backup History from Backup Files to MSDB
This section discusses the procedure that may be used to restore the backup history from a single or a set of backup files in case this information is lost or does not exist in MSDB tables.
For the purposes of this section, we will assume that we have a complete database backup of a database called TESTDB for which the restore history is to be retrieved and saved to MSDB tables.
To restore backup history from backup files
1. | Copy the backup files that are to be restored to the relevant SQL Server machine. |
2. | Open Enterprise Manager and connect to the server where the backups are to be restored. |
3. | Right-click any database and select All Tasks, then select Restore Database... The Restore Database dialog box shown in Figure 5 is displayed.  Figure 5: Restore backup set information |
4. | Select the From Deviceradio button. |
5. | Select the Read backup set information and add to backup historyoption in the Parameterssection. |
6. | Click the Select Devices...button to add the backup file(s) to the list. The Choose Restore Devices dialog box (Figure 6) appears.  Figure 6: Choose Restore Devices dialog box |
7. | Click the Add button and select the backup file(s) for which the backup history is to be loaded. |
8. | Once the files have been selected, click the OKbutton. |
9. | Click the OKbutton in the dialog box shown in Figure 5. This will initiate the restore of the backup history to the MSDB tables. Note It is important to note that this operation does not physically restore the database for which the files are selected in the above step. |
10. | Once the backup history is completely loaded, the dialog box shown in Figure 7 is displayed. Click OK to complete the history restore operation.  Figure 7: Backup history restore confirmation dialog box |
This operation results in the information for TESTDB appearing if a further attempt is made at restoring the database from these backups.
Media Sets and Families
Media sets comprise several individual media. All media in a media set should be of the same type. For example, a 200-GB database might span 3 tapes. The 3 tapes are considered to be a media set.
A media family refers to the collection of media used by an individual backup device. For example, if a 2-TB database is backed up using 4 tape drives (with 5 tapes in each drive), each set of 5 tapes is considered to be a media family. Collectively the 20 tapes would be considered a media set.
Virtual Device Interface
Virtual Device Interface (VDI) is the programming interface for the Backup Restore API. It provides function calls to manipulate the backup and restore functionality in SQL Server.
VDI is used by several third-party tools that provide capabilities to back up SQL Server databases. For more information on this topic, see “Virtual Backup Device Samples” in SQL Server Books Online.
Permissions Required for Backup and Restore
Any logon that requires permissions to perform backup or restore operations should be provided membership in the following SQL Server roles:
Server Role : sysadmin DB role : db_backupoperator, dbo_owner Permissions required for performing restore - Server role : sysadmin, dbcreator DB role : db_owner
Recommended Reading
See the following SQL Server Books Online topics:
Using Multiple Media or Devices
Complete Database Backup
A complete database backup creates a stand-alone image of the entire database. A complete database backup is self-dependent and may be restored to either the same or a new database on the same or a different server. This provides plenty of flexibility at the time when this backup has to be restored.
A complete backup may be restored without the need for any other kind of backup. It may also be performed for databases in any recovery model. Restoring a complete database backup typically would be considered a starting point for a disaster recovery situation where the entire database is lost or damaged.
It is recommended that a complete database backup be performed at regular intervals for all production databases. It is also recommended that a complete backup should be performed for system databases (including master and MSDB) if there are any changes performed to the SQL Server operating environment such as creating or removing databases, configuring security, creating and modifying DTS packages or scheduled jobs, adding and removing linked servers, etc.
Performing Complete Database Backups
A complete database backup may be performed either through SQL Server Enterprise Manager or by using Transact-SQL commands. Complete backups may also be scheduled to be performed at regular intervals. Scheduling may be done through either SQL Server Enterprise Manager or using Transact-SQL commands.
To perform a complete database backup through SQL Server Enterprise Manager
1. | Open Enterprise Manager and connect to the server. |
2. | Expand the Databases folder, then right-click the database that you want to back up.  Figure 9: Backup database through Enterprise Manager |
3. | Select All Tasks, then select Backup Database... as shown in Figure 9. The dialog box shown in Figure 10 is displayed.  Figure 10: Backup Database dialog box |
4. | Provide a name for the backup in the Name text box. Leave the Database – complete radio button selected since we are performing a complete database backup. |
5. | Select the Overwrite existing mediacheck box to initialize the destination file or device or select the Append to mediacheck box to append the current backup to existing file or device. |
6. | To select a destination for the backup, click the Add button. The dialog box shown in Figure 11 is displayed.  Figure 11: Select Backup Destination |
7. | Select an existing file or enter a new file name. Click OK after selecting a file. |
8. | Click the Options tab. The options shown in Figure 12 are presented.  Figure 12: SQL Server Backup Options |
9. | Select the Verify backup upon completioncheck box to verify the backup upon completion. (Please see SQL Server Books Online for more information regarding the RESTORE VERIFYONLY command.) Description of other fields Remove inactive entries from transaction log– truncates the transaction log while performing the backup. If this setting is not checked, SQL Server uses the NO_TRUNCATE option for the backup. This option is available only while performing transaction log backup. Check media set name and backup set expiration- verifies the selected media for the provided media set name to prevent accidental overwrites. Eject tape after backup– ejects the tape from the drive when the backup completes. Backup set will expire– specifies when the backup expires and is no longer restorable. Initialize and label media– erases and labels media sets. Although this option is available for all tape backups, it is most useful when there are multiple tapes forming a media set. |
10. | Once all the necessary options are selected, either click the OK button to start performing the backup, or check the Schedule check box to schedule this operation for periodic execution. If the backup is performed immediately, the Backup Progress dialog box (Figure 13) is displayed while the backup is being performed. If the backup operation should be scheduled, see Modifying the SQL Server Job Schedule later in this paper for more information on how to modify the default schedule.  Figure 13: Backup Progress dialog box |
Upon successful completion of the backup, the informational dialog box shown in Figure 14 is displayed.
Figure 14: Backup complete confirmation
The above functionality can be accomplished through Transact-SQL commands executed from SQL Query Analyzer. An example of such a command is illustrated below.
BACKUP DATABASE northwind TO DISK = 'd:\backups\northwind\nwind.bak'
For more information on the Transact-SQL commands, see BACKUP (T-SQL) in SQL Server Books Online.
Restoring Complete Database Backups
A complete database backup may be restored to the same or a new or different database on the same (or a different) server. The restore operation may be initiated either through either SQL Server Enterprise Manager or the Transact-SQL command window.
It is highly advisable to restore complete database backups at regular intervals, as this is the only means that is currently available to verify the “restorability” of an SQL Server backup.
Restoring a Complete Backup to the Same Database
To restore a complete database backup to the same database
1. | Open SQL Server Enterprise Manager and connect to the server where the backup is to be restored.  Figure 15: Restore database through Enterprise Manager |
2. | Right-click the database and select All Tasks, then select Restore database (as shown in Figure 15). The Restore Database dialog box (Figure 16) is displayed.  Figure 16: Restore Database dialog box |
3. | From the list of databases, select the database for which the backup has to be restored. A list of all backups performed for the selected database is displayed in the Parameters section of the Restore Database dialog box. This information is collected from the history tables in the MSDB database. |
4. | From the list of backups, select the backup to restore, then click the Properties button to display the Backup Set Properties dialog box shown in Figure 17.  Figure 17: Backup Set Properties dialog box The dialog box displays backup properties including backup type, size (in KB), start and finish dates, server name, and media description. |
5. | In the Backup Set Properties dialog box, click OK. |
6. | In the Restore Database dialog box (Figure 18), click the Options tab.  Figure 18: Restore Database Options The Options tab in the Restore Database dialog box provides options to select the final restore state, change the file name, and set tape options for the restore operation. |
7. | Select the appropriate settings in this dialog box. Description of fields Eject tapes after restoring each backup – ejects the tape when the restore operation completes if a tape restore is being performed. Prompt before restoring each backup – displays a dialog box after each backup is restored successfully. This option may be used when restoring multiple backups. Force restore over existing database – forces the files for the existing database to be initialized. This option should be used with caution as it erases the data that exists in the selected database before starting the restore operation. Restore As – shows the original and target physical file names for the database that is being restored. Recovery completion state – determines the final state of the restored database. Undo file– a file required by SQL Server to track incomplete transactions if the backup is restored in standby state. |
8. | To start the restore operation, click OK. The Restore Progress dialog box (Figure 19) is displayed while the restore operation executes.  Figure 19: Restore Progress dialog box |
9. | When the restore operation completes, the dialog box shown in Figure 20 is displayed. To close it, click OK.  Figure 20: Restore database complete confirmation |
Restoring a Complete Backup to a New Database on the Same Server
A new database may be created while restoring a complete database backup. The new database may be created on the same server where the original database was located, or on a different server. In either case, there are several restrictions placed on the database if it is created in this manner:
| • | Physical file structure may not be modified while restoring |
| • | Logical file names may not be changed |
| • | Physical files created must be the same size as when the backup was performed |
If you intend to create a new database, as part of the restore operation, on the same server where the original database still resides, follow this procedure.
To restore a complete backup to a new database on the same server
1. | Open SQL Server Enterprise Manager and connect to the server where the backup is to be restored. |
2. | Right-click any database and select All Tasks, then select Restore Database to open the dialog box shown in Figure 21.  Figure 21: “Restore Database As” dialog box |
3. | In the Restore as database: text box, enter the name of the new database that you wish to create. |
4. | Under Parameters, select the database for which you intend to restore the backups (in the Show backups of database list box). A list of backups for the database are displayed in window. |
5. | Select the backup that you would like to restore and click Properties to verify the properties for that backup. See the Properties dialog box in Figure 17 for an illustration. |
6. | Click the Options tab.  Figure 22: “Restore Database As” Options dialog box |
7. | If necessary, modify the file name and location of the database files listed in the Restore Aslist. The first part of the file name is used as the logical name for the respective file. In the example in Figure 22, the first file will have the physical file name of nwind_new_log.ldf and the logical name of nwind_new_log. |
8. | Select all the other options from this dialog box as necessary. For an explanation of these options, see Figure 18. |
9. | To start the restore operation, click OK. A progress dialog box is displayed and when the restore operation is finished, a confirmation dialog box is displayed. A new database called nwind_new is added to the Enterprise Manager window. |
This functionality can also be accomplished by using Transact-SQL commands. Assuming that the database backup file name is c:\backups\northwind\nwind.bak, the following command will restore the database to nwind_new:
RESTORE DATABASE nwind_new FROM DISK = 'c:\backups\northwind\nwind.bak' WITH MOVE 'northwind' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new.mdf' MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQL Server\Data\nwind_new_log.ldf'
Restoring a Complete Backup to a New Server
SQL Server databases may be backed up on one server and restored to another server. If the target database does not exist on the second server, it may be created before the restore is initiated. However a new database can be created while restoring the backup.
If a new database is created while restoring the backup, all the restrictions mentioned in Restoring a Complete Backup to a New Database on the Same Server apply.
The steps in this section demonstrate one of the many ways that someone may use to restore the database onto a different server. Another means of achieving this same functionality would be to restore the backup history using the procedure explained in Adding Backup History from Backup Files to MSDB and then using the procedure listed in Restoring a Complete Backup to a New Database on the Same Server using the SQL Enterprise Manager GUI.
To restore a database backup to a different server to a new database
1. | Copy the database backup file to the target machine or share it on the network so that it is accessible from the server. |
2. | Open Enterprise Manager and connect to the server to which the backup is to be restored. |
3. | Right-click any database and select All Tasks, then select Restore Database to display the Restore Database dialog box shown in Figure 23.  Figure 23: “Restore from device” dialog box |
4. | Enter the name of the new database that the backup process will create and restore the backup to, in the Restore as database: list box. |
5. | Select the From deviceradio button. |
6. | Click the Select Devices... button.  Figure 24 : Choose Restore Devices dialog box |
7. | In the Choose Restore Devicesdialog box, select either the Disk or the Taperadio button. In the dialog box in Figure 24, the Tape option is dimmed (unavailable) because the backup device does not have a tape drive configured. For the purposes of this example we will assume that we are restoring the backup from a disk device. |
8. | Click the Add button to display the dialog box shown in Figure 25.  Figure 25: Choose Restore Destination dialog box |
9. | Enter the file name of the backup file that is to be restored. If the file exists on a network share, provide the UNC share name in the File name text box. Note It is recommended that the backup be copied to the server and a local path be used to restore the database. |
10. | Click the OKbutton. The Choose Restore Devicedialog box, displayed in Figure 24, is now updated with the selected file. |
11. | Click the OK button to return to the Restore Databasedialog box. The Devices list is now updated with the file selected in the previous step. |
12. | Click the Options tab and select appropriate options for the restore operation. The file names of the physical files may be changed at this time. For more information on the fields in this dialog box, see steps 6 and 7 in Restoring a Complete Backup to a New Database on the Same Server. |
13. | Click OK in the Restore Database dialog box. This starts the restore process. Progress and confirmation dialog boxes are displayed as SQL Server processes and completes the restore of the database. |
14. | Click OKin the confirmation dialog box. |
This restore operation may also be accomplished through Transact-SQL commands. The command would be similar to the one demonstrated in Restoring a Complete Backup to a New Database on the Same Server.
Performing a Partial Database Restore
Partial Database Restore is newly added functionality in SQL Server 2000. It allows for individual filegroups to be restored to new databases. This functionality is implemented in SQL Server using new options for the RESTORE Transact-SQL command.
For a better understanding of filegroups, see “File/Filegroup Backup” later in this paper.
A filegroup may be restored partially only from a complete backup. A file/filegroup backup does not contain transaction log backup and hence cannot be used to restore the particular filegroup partially. All other filegroups in the database (except Primary) are marked OFFLINE when this restore mechanism is used. It should be noted that all files in the Primary filegroup are always restored in any Partial Database Restore situation.
Performing a Partial Restore
For the purposes of this demonstration we will assume that:
| • | We have a database called files that has two filegroups—fg1 and fg2 (see the script in Appendix A). |
| • | Database files has a complete backup stored in file called d:\backup\files.bak |
We need to restore the contents of filegroup fg2 to a new database called files_fg2.
Open SQL Query Analyzer and perform the query shown in Figure 26 to get the names of files that constitute the complete backup.
We will need to restore files belonging to the Primary and fg2 filegroups. Execute the command shown in Figure 27 from the SQL Query Analyzer window.
Apply any further Transaction log or differential backups that might be available.
Once the database is recovered, it may be accessed in a normal manner. All other filegroups in the database are marked OFFLINE. Any attempts to perform queries on objects that reside on other filegroups in the database generate the error shown in Figure 28.
Recommended Reading
See the following SQL Server Books Online topics:
Partial Database Restore Operations
See the following Microsoft Knowledge Base article:
Q221465 INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q221465
Transaction Log Backup
An SQL Server database consists of two components—data file(s) and transaction logs. A transaction log captures the modifications made to the database. An SQL Server database must have at least one transaction log file.
A simple transaction may place several records in the transaction log. Each of these records is known as a log record and is assigned a unique identification number known as the log sequence number. Log records that belong to the same transaction are linked together through the log sequence number.
If SQL Server service shuts down unexpectedly, upon restart the recovery process examines the entries in the transaction log and if there are transactions that have not been rolled forward completely, the recovery process rolls back the changes performed as part of these incomplete transactions. This operation is extremely important as it forms the basis of transactional recovery at startup. Entries in the transaction log are also used if transactional replication is configured for the specific database.
A transaction log backup backs up all transactions since either the previous transaction log backup, or the complete database backup if there have been no transaction log backups performed for the database in the past. This backup may then be used to apply the backed-up changes, in case disaster recovery is required. Transaction log backups may only be applied to a database in an unrecovered state. A database may be in an unrecovered state if it is being restored from a set of backups as part of a disaster recovery procedure, or if it is configured as a standby database on a warm backup server.
A transaction log backup also truncates the inactive portion of the transaction log, unless the database is configured as a Publisher in transactional replication and there are transactions pending propagation to Subscribers.
Each transaction log backup contains a First and Last log sequence number (LSN). Consecutive transaction log backups should have sequential LSNs for the boundary log records. These LSN values may be examined using the RESTORE HEADERONLY command. If LastLSN from the previously restored transaction log backup does not match the FirstLSN from the backup that is currently being restored, the restore operation fails with the following error:
Server: Msg 4305, Level 16, State 1, Line 1
This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log.
If the above message is generated while restoring a particular transaction log backup, which is part of a set of transaction log backups that are to be restored, any attempts to restore further transaction log backups will fail with this message. There could be several reasons for consecutive transaction log backups being out of sequence. Some of the most common reasons noted from support experience have been:
| • | The database recovery model has been changed to Simple and back to either Full or Bulk-Logged. Switching the recovery mode to Simple causes the transaction log to be truncated. |
| • | Another transaction log backup was performed between the previous successfully restored backup and the one generating this message. |
| • | The transaction log was manually truncated between the two backups. |
| • | The database was in Bulk-Logged recovery model and non-logged operations were performed. |
| • | Transaction log backups are not allowed for databases in Simple Recovery model. While in Simple Recovery model, a database’s transaction log is truncated every time a CHECKPOINT is invoked for the database. |
| • | Transaction log backups provide the possibility of performing a point-in-time restore or point-of-failure restore. |
Performing Transaction Log Backups through Enterprise Manager
To perform a transaction log backup
1. | Open SQL Enterprise Manager. Connect to the server and expand Databases. |
2. | Right-click the database for which a transaction log backup has to be performed and select All Tasks, then select Backup database... The dialog box shown in Figure 29 is displayed.  Figure 29: Transaction Log Backup |
3. | You can enter a name for the backup in the Name text box and a description for this backup in the Descriptiontext box. These two fields are optional and may be left blank. |
4. | To perform a transaction log backup, select the Transaction log radio button. |
5. | Select a destination for the backup. This could be either an SQL Server backup device, tape, or a disk file. For the purposes of this example, we have selected a disk file called d:\backups\nwind\nwind_log.bak. For more information, see the procedure in Performing Complete Database Backups in this paper. |
6. | To remove any previous files with this name and create a new file, select the OverwriteExisting Mediaradio button. |
7. | Check the Schedulecheck box if this operation has to be scheduled for periodic operation. For more information on how to modify the schedule for an operation, see Modifying the SQL Server Job Schedule in this paper. |
8. | To view and, if necessary, modify the transaction log backup options, click the Optionstab.  Figure 30: Transaction Log Backup Options For more information on the fields in the Options tab, see Figure 9. |
9. | Once all the options have been selected, click the OKbutton to start the backup. A progress dialog box is displayed while the backup is being performed. When the backup is finished, a confirmation dialog box is displayed. |
10. | Click OKin the confirmation dialog box to complete the backup operation. |
Restoring Transaction Log Backups to the Same Server
As mentioned previously, to restore a transaction log backup, a complete backup must be restored in an unrecovered state. For the purposes of this example, we will continue with the example Restoring a Complete Backup to a New Database on the Same Server where a new database called Nwind_New was created from a backup of Northwind database.
For this example we would have to select the Leave Database non-operational but able to apply further transaction log backups option under the Leave Database Operational setting when restoring the complete backup. This option is displayed in Figure 18.
To restore transaction log backups to the same server
1. | Open Enterprise Manager, connect to the server, and expand Databases. |
2. | Right-click Nwind_New database and select All Tasks, then select Restore Database... The dialog box shown in Figure 31 is displayed.  Figure 31: Restore log to new database |
3. | In the Show backups of database list, select Northwind. This updates the information displayed in the window. It may be noticed that the transaction log backup corresponding to the complete backup that was loaded initially to create the NWIND_NEW database is selected by default. |
4. | You can view properties for the log backup by selecting the Northwind Log Backup and clicking the Propertiesbutton. |
5. | To view and, if necessary, modify any option relating to the restore operation, click the Options tab. For a detailed description of the options available on this tab, see the descriptions provided for Figure 18. |
6. | To start the restore of the transaction log, click OK. A progress dialog box is displayed while the backup is being restored. When the restore finishes successfully, a confirmation dialog box is displayed. |
7. | To complete the restore operation, click OK in the confirmation dialog box. |
Restoring Transaction Log Backups to a Different Server
Since a transaction log backup may only be applied if a database is in an unrecovered state, if the restore operation is being performed on a different server, the backup history does not exist. In this case, either Enterprise Manager or Transact-SQL commands could be used to perform the restore of the complete database backup. Alternatively, the backup history could be loaded into the new server’s MSDB database using the procedure described in Adding Backup History from Backup Files to MSDB later in this paper.
For the purposes of this paper, we will consider this procedure as performed through SQL Enterprise Manager. We will continue this example from the example provided in Restoring a Complete Backup to a New Server later in this paper.
To restore transaction log backups to a different server
1. | Open SQL Server Enterprise Manager and connect to the server. |
2. | Expand Databases. Right-click the database for which the transaction log backup is to be restored and select All Tasks, then select Restore Database... The dialog box shown in Figure 32 is displayed.  Figure 32: Restore transaction log backup to new server |
3. | To select the backup file(s) containing transaction log backup(s) to be restored, click the Select Devices... button. For more information on this step, see the procedure explained for Figure 18. |
4. | Select the Transaction logradio button under Restore backup set. |
5. | Click the Optionstab and select appropriate options for the restore. If this is the final transaction log backup, select the Leave database operational. No additional transaction logs can be restoredoption. If there are further backups to be applied after this one, select one of the other two options for the Recovery completion state. |
6. | To start the restore operation, click OK. Progress and confirmation dialog boxes are displayed while the backup is being restored and when the backup is restored. |
7. | To complete the restore, click OKin the confirmation dialog box. |
Restoring Transaction Log Backups to a Point in Time
Transaction log backups may be restored to a point-in-time. This functionality is available only through the use of transaction log backups. There are certain restrictions placed upon using point-in-time recovery as follows:
| • | Point-in-time recovery may not be used in conjunction with file/filegroup restore. The tail of transaction log has to be applied in full when recovering file/filegroups from file/filegroup backups. For more information, see “File/Filegroup Backups” later in this paper. |
| • | Point-in-time recovery is only available as the last step in a recovery situation. This means that no further backups may be restored if point-in-time recovery is used. The database is recovered immediately even if the NORECOVERY or STANDBY option is specified. |
Point-in-time recovery is implemented using the STOPAT option with the RESTORE LOG Transact-SQL statement. The SQL Enterprise Manager dialog box shown in Figure 33 is used to perform a point-in-time restore.
This dialog box is displayed when the Point in time restorecheck box is selected in the dialog box shown in Figure 28.
Figure 33: Point In Time Restore dialog box
Select the appropriate date and time to stop the transaction log restore operation through this dialog box. Once the date time is selected, click OK to continue with the restore operation.
Note Since this procedure results in a different ending transaction after the restore is complete, a full database backup should be performed before any further transaction log backups.
Restoring Transaction Log Backups to a Named Transaction
SQL Server 2000 provides functionality to restore a transaction log backup up to and including or excluding a certain transaction. This functionality has been implemented with new options for the RESTORE LOG command—STOPATMARK and STOPBEFOREMARK.
Restoring a transaction log to a named transaction is bound by the same restrictions as point-in-time restore. Additionally, recovery to a named transaction is available only if the specific transaction was started with a name. The following code demonstrates how to create a simple named transaction that updates the Customers table in the Northwind database.
BEGIN TRAN Demo UPDATE Customers SET CITY = ‘Hamburg’ WHERE NAME = ‘Tony Munitz’ COMMIT TRAN Demo
If this transaction is the offending transaction and a customer would like to perform recovery on the database, excluding this transaction and any other transactions that were performed after this transaction, we could use the following command:
RESTORE LOG NorthWind_Test FROM DISK = 'd:\backups\TLOG_23.BAK' WITH STOPBEFOREMARK = 'Demo'
Note STOPBEFOREMARK and STOPATMARK options may be used with the AFTER clause to stop recovery after a given datetime for a named transaction.
Restoring to a Point of Failure
Under certain circumstances it may be possible to recover the database to a point where it failed. Recovery to a point of failure is available only when the database is in the Full or Bulk-Logged Recovery models. Restoring a database to the point of failure requires the following backup components:
1. | A backup of the tail of transaction log. |
2. | A complete database or file/filegroup backup. Optionally we may also need differential or file differential backups. |
3. | A sequence of all transaction log backups from the oldest complete (or file/filegroup backup) or the latest differential (or file/filegroup differential) backup restored. |
Step 1: Backup of the tail of transaction log
To perform a backup of the tail of transaction log if database files are damaged, see the procedure in article Q253817 in the Microsoft Knowledge Base. As an example, the following command may be used to back up the log when database files are damaged:
BACKUP LOG <dbname> TO DISK = 'd:\backup\Tail_TLOG.bak' WITH NO_TRUNCATE
Step 2: Restore complete database or file/filegroup backup
If the damaged files were backed up as part of a file/filegroup backup, locate the latest file/filegroup backups. If no filegroup backups have been performed or if they are not available, locate the most recent complete backup.
If files from only a certain filegroup are damaged, and filegroup backups along with transaction log backups exist for the damaged filegroup, restore the filegroup backup.
If complete database is damaged or the filegroup backups do not exist for the damaged filegroup(s), restore the most recent complete backup, as explained in “Complete Database Backup” earlier in this paper.
Restore any further differential backups that may have been performed after either the complete backup or the file/filegroup backup.
Step 3: Apply all transaction log backups
If in the previous step a complete backup was restored, restore all transaction log backups performed after the respective complete backup and apply the tail of transaction log performed in step 1.
If file/filegroup backup was restored in the previous step, apply all transaction log backups following the latest file/filegroup or differential file/filegroup backup.
Complete the restore process by applying the tail of transaction log backup in step 1.
Recommended Reading
See the following SQL Server Books Online topic:
Recovering to a Named Transaction
Differential Backup
A differential backup backs up only modified extents since the last complete backup. By definition, differential backups are cumulative. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup.
Differential backup functionality has been enhanced in SQL Server 2000. SQL Server 2000 uses a bitmap that contains one bit for each extent in a database. This bit is set to 1 if any pages within that particular extent have been modified since the previous complete backup. When a differential backup command is issued, SQL Server examines this bitmap. It accesses only the extents that are flagged as having been modified through this bitmap and writes them to a backup file or device. This functionality greatly improves the performance of differential backups.
Differential backups may be considered as an alternative for databases that are large and are modified infrequently. These would include data warehouse type of databases.
Differential backups have several limitations:
| • | They do not provide point-in-time restore capabilities. |
| • | Differential backups may not be restored by themselves. They may only be restored after a complete database backup is restored. |
| • | You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database. Differential database backups may not be performed on the master database. If an attempt is made to perform a differential backup on the master database, the following error is raised: Server: Msg 3024, Level 16, State 1, Line 1 |
| • | Differential backups are not allowed for the master database since they require that complete backups be applied without recovery as the first step. When restoring the master database, restore without recovery is not allowed. |
It should be noted that although differential backups may provide fast backup times in some situations, using them with highly active OLTP databases may result in backup times that may be comparable to complete database backups. As mentioned before, differential backups back up only extents for which any pages have been modified. If the database activity is such that 80-90% of extents have been modified, the time that a differential database backup may take may be very close to the complete backup.
Performing Differential Backup
To perform a differential backup
1. | Connect to the server and expand Databases. |
2. | Right-click the database and select All Tasks, then select Backup Database... The dialog box shown in Figure 34 is displayed.  Figure 34: Differential database backup settings |
3. | In the Database list box, select the database name for which to perform a differential backup. Optionally, enter a name in the Name text box and/or description for the backup in the Description text box. |
4. | To perform the differential backup, select the Database – differentialoption. |
5. | Enter a backup file name following the steps outlined in Performing Complete Database Backups. |
6. | Click the Options tab and select appropriate options for the backup operation. |
7. | To start the backup operation, click the OK button. This operation may also be scheduled for periodic execution by checking the Schedule check box. For more information on modifying the default schedule for a job, see Modifying the SQL Server Job Schedule. While the backup operation is progressing, a progress dialog box, similar to the one in Figure 13, is displayed. Once the backup completes successfully, a confirmation dialog box, similar to the one shown in Figure 14, is displayed. |
8. | To complete the backup operation, click OK in the confirmation dialog box. |
This task may also be achieved through Transact-SQL commands. The following command would perform the same operation as in the previous procedure:
BACKUP DATABASE Northwind TO DISK = 'D:\backups\nwind\nwind_diff.bak' WITH DIFFERENTIAL
Restore to the Same Server
For the purposes of this section, we will continue from the complete database restore performed in Restoring a Complete Backup to the Same Database. The only difference would be that we would need to select the Leave database non-operational but able to restore additional transaction log backups option in the Recovery completion section in the Options tab on the dialog box displayed in Figure 18.
To restore to the same server
1. | Open Enterprise Manager and connect to the server. |
2. | Right-click the database for which the differential backup is to be restored and select All Tasks, then select Restore Database... to display the dialog box shown in Figure 35.  Figure 35: Differential database restore settings |
3. | In the Parameters section, select Northwind database in the Show backups for databaselist box, since this is the source database for which the backups are to be applied. |
4. | Select the time when the backup was performed in the First backup to restore list box. The window below this list is now refreshed with all the backups that have been performed from the selected entry in Step 4. In this particular case, there is a single complete backup and a single differential backup. |
5. | Since the complete backup has already been restored, select only the differential backup in the list. Database properties may be viewed by selecting the backup and clicking the Propertiesbutton. |
6. | Click the Options tab and select appropriate options. For more information on these options, see the descriptions listed for Figure 18. For the purposes of this demo, we will assume that the database needs to be recovered upon successfully applying the differential backup. |
7. | To start the restore operation, click the OK button. A progress dialog box similar to the one shown in Figure 19 is displayed while the restore operation is progressing. Upon successful completion of the restore operation, a confirmation dialog box, similar to the one displayed in Figure 20, is displayed. |
8. | To complete the restore operation, click the OK button. |
The above operation may also be carried out by using the following Transact-SQL command:
RESTORE DATABASE Nwind_New FROM DISK = 'd:\backups\nwind\nwind_diff.bak' WITH RECOVERY
Restore to a Different Server to a New Database
The procedure to restore a differential backup to a different server differs from the steps explained in Restore to the Same Server in the “Differential Backup” section in this paper because the backup history does not exist.
Another means to have the database restored to a different server would be to load the backup history for this database using the procedure described in Adding Backup History from Backup Files to MSDB and then use the SQL Enterprise Manager GUI to restore the backup using the procedure explained in Restoring a Complete Backup to a New Database on the Same Server.
For the purposes of this example, we will assume that a complete backup has been restored without recovery. See the procedure explained in Restoring a Complete Backup to a New Database on the Same Server; however, select the Leave Database Non-operational but able to restore additional transaction logs radio button shown in the dialog box in Figure 22.
To restore to a different server to a new database
1. | Open Enterprise Manager and connect to the server. |
2. | Expand databases. Right-click the database to which the differential backup has to be applied, and select All Tasks, then select Restore Database... The dialog box shown in Figure 36 is displayed.  Figure 36: Restore differential backup to new database settings |
3. | In the window that is displayed, select the From device option in the Restore section. |
4. | Add the backup device in the Deviceslist following the procedure explained for Figure 24. |
5. | Select Database – differentialunder Restore backup set. |
6. | Click the Option tab and select appropriate options for the restore operation. |
7. | To start the restore operation, click the OK button. A progress dialog box is displayed while the restore is being performed. When the restore completes, a confirmation dialog box is displayed. |
8. | To complete the restore operation, click OK in the confirmation dialog box. |
File/Filegroup Backup
SQL Server organizes a database in several files. By default, a new database is created with one data file and one log file. Other data files may be added either while creating the database or once the database is operational. The database files for SQL Server typically will have .mdf or .ndf file extensions. Ideally, only the first database file that SQL Server creates should have the .mdf file extension. This is a special database file since it contains a database header and all the system tables. All other database files should ideally be given an extension of .ndf.
A filegroup is a logical grouping of SQL Server database files. By default, SQL Server creates a filegroup called Primary. SQL Server adds the first database file (with the extension of .mdf) to the Primary filegroup. Other filegroups may be created at the time the database is created or after the database has already been created. Database files should be added to the filegroup at the time they are created. Once a file has been created and added to a database, the filegroup that it belongs to may not be modified.
SQL Server provides the functionality to back up individual files/filegroups within a database.
Note A file/filegroup backup does not back up the transaction log portion of the database. This is a significant difference between file/filegroup backup and complete backup. Hence a filegroup backup cannot be used as the first step in a recovery situation.
File/filegroup backups may be used to restore the individual file/filegroup if some or all of the files within the respective filegroup are damaged. The following restrictions are enforced when using file/filegroup backups:
| • | A file/filegroup backup may only be restored to the same database it was backed up from. |
| • | All transaction log backups (including the tail) for the database should be available and restorable. It is implied that this would not be possible to do on a database that is in Simple Recovery model. |
| • | Point-in-time recovery is not permitted when restoring file/filegroup backups. All transaction log backups have to be restored in their entirety. |
Performing File/Filegroup Backup (Enterprise Manager)
To create the database used in the examples in this section, use the script provided in Appendix A.
To perform a file/filegroup backup
1. | Open Enterprise Manager and connect to the server where the database resides. |
2. | Right-click the database and select All Tasks, then select Backup database. The dialog box shown in Figure 37 is displayed.  Figure 37: File/filegroup backup general settings |
3. | Select the File and filegroup radio button in the Backup section of the dialog box. Click the ellipses button next to this option. This opens the dialog box shown in Figure 38.  Figure 38: Specify file/filegroup to back up |
4. | In the Specify Filegroups and Filesdialog box, select the files/filegroups that you would like to back up. For the purposes of this example, we will back up filegroup fg1. Click the OKbutton once the files/filegroups have been selected. |
5. | Click the Addbutton in the SQL Server Backupdialog box. To add the destination file for the backup, follow the steps for Figure 11. |
6. | Click the Optionstab in the SQL Server Backupdialog box. Select options as necessary. For more information on the options in this dialog box, see the explanation for Figure 12. |
7. | When you are ready to perform the backup, click the OK button. A progress dialog box is displayed while SQL Server performs the backup. When the backup finishes successfully, a confirmation dialog box is displayed. |
8. | Click the OK button in the confirmation dialog box. |
Filegroup backups may also be accomplished with Transact-SQL commands. The above functionality could also have been achieved by using the following Transact-SQL command:
BACKUP DATABASE files FILEGROUP = 'fg1 ' TO DISK = 'd:\backups\files\fg1.bak' WITH INIT
Restoring Filegroup Backups if Filegroup Files are Damaged
For this section we will assume that the disk where the database files for filegroup fg1 were stored was damaged and we lost both files for this filegroup.
To restore filegroup backups if files are damaged
1. | Open an SQL Query Analyzer window and connect to the server where the files database resides. |
2. | Select master database from the list box and enter the command shown in Figure 39. (If this step is not possible due to damage to the transaction log, the entire database must be restored from the complete backup, and then all transaction log backups should be restored to bring the database up to the point of the last successful log backup.) |
3. | Right-click the files database and select All Tasks, then select Restore Database... |
