Backing Up a Database or Transaction Log

You should back up a database after you create it, and then maintain a regular backup schedule to ensure smooth recovery in case of database or media failure. SQL Enterprise Manager allows you to set and maintain a schedule of automatic backups. Scheduling can be accomplished at the same time you define a backup. You can also back up manually at any time.

If a transaction log is on a separate device from its database, you can back up the transaction log separately. You should back up a transaction log more frequently than its database.

The SQL Server dump is a dynamic dump ¾ it can take place while the database is active. The dump captures the state of the data as it is at the moment the statement is executed; no partial transactions are reflected. Any data changes made after the dump begins are not reflected in the dumped database. Performing a dump can slow the system down somewhat, so you may want to run it when the database is not being heavily updated.

You should maintain regular backups of the master database, the msdb (scheduling) database, all user databases, and (if the server is configured as a replication distributor) the distribution database.

    To back up a database or a transaction log

1. From the Server Manager window of SQL Enterprise Manager, select a server.
2. From the Tools menu, choose Backup/Restore.

   The Database Backup/Restore dialog box appears.

   

3. Select the device(s) to back up to.

   In the Dump Devices box, select a device, and then choose Add. To perform a striped dump (a multiple dump device backup), repeat this step for each dump device that will be part of the backup. The added devices move to the Backup Destination list.

   If necessary, you can create a new dump device by choosing New and completing the Create Dump Device dialog box that appears.

   The DISKDUMP device is a special device. Backing up to DISKDUMP sends the data to the NULL device, which dumps data without saving it. You can use the DISKDUMP device name as a bit bucket in conjunction with database or transaction log backups.

   Important You cannot retrieve dumps sent to the NULL (DISKDUMP) device.

   You cannot back up to diskette dump devices when using SQL Enterprise Manager. For information about backing up to diskette dump devices, see the Microsoft SQL Server Transact-SQL Reference. For information about dump devices, see Chapter 5, Managing Devices.

4. To add a volume name for a device, type up to six characters (letters or numbers) in the Volume box of the Backup Destination list.

   In general, you do not need to provide volume names for dumps. You may provide one if this is the first dump on a device or if Append is not selected. The volume name is in effect for all subsequent dumps appended to the device. Entering a name is optional for subsequent dumps, but if specified it must match the initial volume.

   To view volume and header information for a disk or tape dump device, select the device from the Dump Devices list and then choose the Info button.

5. From Database list, select the database to back up.
6. Select either Entire Database or Transaction Log.
   • Entire Database backs up the database and its transaction log.
   • Transaction Log backs up only the transaction log.

     To use this option, the transaction log must be on a separate device from the database. For more information about setting this up, see Chapter 6, Managing Databases.

7. Select or clear the backup options.
   • Append to Device retains any backups currently on the dump device, and adds the new backup. To overwrite any existing backups on the device with the new backup, clear this option.

     For SQL Server Version 6.0, backups can be appended to any disk or tape dump device, allowing you to keep backups of a database and its transaction logs in one physical location.

     If you are using a tape that contains data in a foreign format (not SQL Server backup files), you must clear this option, or the tape will be rejected.

   • Skip Tape Header specifies that the server will not read the ANSI header on a tape. To tell SQL Server to read any existing ANSI tape labels on the tape(s) you are writing, clear this option.

     For example, if the ANSI label of a tape warns that it has not expired or that you don't have permission to write to it, selecting this option causes SQL Server to ignore the warning. Or if the tape you are writing to is new (and therefore has no ANSI label), SQL Server writes a new label on the first try. Selecting this option prevents unnecessary retries as SQL Server tries to find a label.

     This allows you to choose between the security of paying attention to ANSI labels or the convenience of ignoring them. In either case, SQL Server still writes the labels.

     Setting this option for a particular backup overrides the setting defined for the tape dump device when it was created.

   • Unload Tape at End automatically rewinds and unloads the tape when a backup to a tape device completes.

     If you will be performing multiple dumps to a single tape, clear this option. If this is a single backup or the last backup of several to a tape, select this option.

   • Expires on sets the backup to expire on a specific date. If you select this option, you must also set the expiration date in the adjacent box.

     An existing dump cannot be overwritten until it expires. However, when Append to Device is selected, this option is informational only.

   • Expires after sets the backup to expire after an elapsed number of days. If you select this option, you must also type the number of days in the adjacent box.

     An existing dump cannot be overwritten until it expires. However, when Append to Device is selected, this option is informational only.

   • No expiration date specifies that the backup has no expiration date and can be overwritten at any time. This is the default.
8. Either execute the backup or schedule it for execution.
   • To immediately execute the backup, choose the Backup Now button. For more information, see Executing an Immediate Backup, next.
   • To set up scheduled execution, choose the Schedule button and complete the Schedule Backup dialog box that appears. For more information see Scheduling a Backup, later in this chapter.

You cannot back up a transaction log separately after enabling the Select Into / Bulk Copy database option and making unlogged changes to the database with SELECT INTO or a bulk copy operation. Under these conditions, do a database backup instead.

For more information about backing up databases, see More About Backing Up and Restoring, later in this chapter. You can also back up a database or transaction log using the DUMP statement. For information, see the Microsoft SQL Server Transact-SQL Reference.