Lesson 2: Performing Backups | Microsoft SQL Server 7.0 System Administration Training Kit

[Previous] [Next]

Once the backup strategy is in place and the medium for the backup devices is decided on, you are ready to perform the actual backups. Backups can be performed using the SQL Server Enterprise Manager or Transact-SQL.

After this lesson, you will be able to

Perform a database backup
Perform a transaction log backup
Perform a differential backup
Perform a file or filegroup backup

Estimated lesson time: 90 minutes

Using the BACKUP Statement

You can perform backup operations with SQL Server Enterprise Manager, the Backup Wizard, or Transact-SQL. You should be familiar with certain backup options whenever you use any of the SQL Server backup methods.

The syntax for the BACKUP DATABASE statement with options is as follows:

 BACKUP DATABASE {database_name | @database_name_var} TO <backup_device> [, …n] [WITH      [[, ] FORMAT]     [[,] {INIT | NOINIT}]     [[, ] RESTART]     [,] MEDIANAME = {media_name | @media_name_variable}] ]

NOTE
The BACKUP statement replaces the DUMP statement found in previous versions of SQL Server. DUMP is still supported but for backward compatibility only. You should use the BACKUP statement.

Using the MEDIANAME Option

You can use the MEDIANAME option of the BACKUP statement or the Media Name option in SQL Server Enterprise Manager to specify the name of the media set for the backup. If specified for a new backup device or a backup device that is being formatted, this name will be written to the media header. If specified for subsequent backups, SQL Server will check that the name specified matches the name on the media and will cause the backup to fail if the names do not match, preventing accidental use of the incorrect media.

When you use multiple devices to back up a database, the media set name is also used to associate the backup devices with one another. It can be used to check that the correct media are used together for subsequent backups.

Names can have up to 128 characters if specified with the MEDIANAME option of the BACKUP statement and up to 64 characters if specified with the Media Name option in SQL Server Enterprise Manager.

Using the INIT or NOINIT Option

When you back up a database, you can overwrite existing data on the backup device or append the backup set after existing data on the backup device.

Use the NOINIT option to append a backup set to an existing backup device. This is the default.

If you use the INIT option, SQL Server writes the backup set at the beginning of the device, overwriting any existing data on the backup media but retaining the media header.

The backup operation fails and data is not overwritten if

The EXPIREDATE option was specified for backup sets on the backup device and the backup sets on the backup device have not yet expired.

The media_name parameter that you specified in the MEDIANAME option does not match the media name of the backup device.

You attempt to overwrite one member of a previously named media set. SQL Server detects that the device is a member of a media set and that overwriting its contents would make the whole set unusable.

Using the FORMAT Option

You use the FORMAT option to overwrite the contents of a backup device and split up a media set. When you use the FORMAT option

A new media header is written on all devices that are used for this backup operation.

SQL Server overwrites both the existing media header and the contents of the backup device.

Use the FORMAT option carefully. Formatting only one backup device of a media set renders the entire media set unusable. The media name of the media is not checked when using the FORMAT option, making it possible to change the media name of an existing device. This is another reason to use the FORMAT option with caution; you will not be warned if you are accidentally using the wrong media.

Using the RESTART Option

If you want SQL Server to restart an interrupted backup operation from the point of interruption, use the RESTART option. You must restart the backup process manually by executing the original BACKUP statement with the RESTART option.

Backing Up to a Tape Device

Tapes are a convenient medium for backups because they are inexpensive, provide a large amount of storage, and can be stored off-site for data safety and security.

When you back up to a tape, the tape drive must be attached locally to SQL Server.

Recording Backup Information on the Tape Label

When you back up to a tape, SQL Server records the following backup information on the tape label:

Database name

Time

Date

Type of backup

Storing SQL Server and Non_SQL Server Backups

SQL Server uses a standard backup format called Microsoft Tape Format to write backups to tape. Consequently, both SQL Server and non_SQL Server data can be stored on the same tape.

This does not mean that SQL Server can use backups written to a tape by another application. It simply means that SQL Server backups can exist on the same tape as backups written by other applications that use the MTF format. For example, you could perform a SQL Server backup to disk and then use Windows NT Backup to back up the disk files to tape. To restore the files, you would have to use Windows NT Backup to restore the files to disk, and then use SQL Server to restore the SQL Server backup from the disk backup device; you cannot restore the Windows NT backup directly from the tape with SQL Server.

Specifying Tape Options

When you back up to a tape, you can use options that are specific to this backup medium. This section describes these tape-specific options.

The UNLOAD Option

SQL Server automatically rewinds and unloads the tape from the tape drive after the backup is complete. The UNLOAD option is the SQL Server default and remains set until you select the NOUNLOAD option.

The NOUNLOAD Option

Use the NOUNLOAD option if you do not want SQL Server to rewind and unload the tape from the tape drive automatically after a backup. The NOUNLOAD option remains set until you select UNLOAD.

The BLOCKSIZE Option

Use the BLOCKSIZE option to change the physical block size in bytes if you are overwriting a tape with the FORMAT or SKIP and INIT options. When you back up to a tape, SQL Server selects an appropriate block size. You can override this selection by using the BLOCKSIZE option.

The FORMAT Option

Use the FORMAT option to write a header on all of the volumes (disk files or tapes) that are used for a backup. SQL Server overwrites all headers and backups on the media. The header includes information that is found in the MEDIANAME and MEDIADESCRIPTION options.

When you use the FORMAT option to back up to a tape device, the INIT and SKIP options are implied, and therefore you do not need to specify these options.

Use this option with caution, as the new backup will overwrite the media without checking the existing header and password (if any) on the media. If the media are part of an existing media set that has been used for a striped backup set, that backup set will be unusable.

The SKIP Option

Use the SKIP option to skip headers. This option causes SQL Server to ignore any existing ANSI tape labels on the tape device. The ANSI label of a tape can provide warning information about the expiration date of the tape, as well as enforce write permissions.

The NOSKIP Option

Use the NOSKIP option if you want SQL Server to read ANSI tape labels. This is the SQL Server default.

Performing a Complete Database Backup

A complete database backup serves as your baseline in case of a system failure. When you perform a complete database backup, SQL Server backs up everything in the database, including any portions of the transaction log needed to ensure data consistency when the backup is restored.

The following example creates a named backup device with the logical name nwndbac and performs a complete database backup.

 USE master EXEC sp_addumpdevice 'disk', 'nwndbac',      'c:\mssql7\backup\nwndbac.bak' BACKUP DATABASE northwind TO nwndbac

The example that follows performs a complete database backup to the nwndbac device and overwrites any previous backups on that device.

 BACKUP DATABASE northwind TO nwndbac WITH INIT

The next example appends a complete database backup to the nwndbac device. Any previous backups on the device are left intact.

 BACKUP DATABASE northwind TO nwndbac WITH NOINIT

The following example performs a complete database backup to a temporary backup device.

 BACKUP DATABASE northwind TO  DISK = 'D:\Temp\Mytempbackup.bak'

Exercise: Clearing Options Set for the StudyNwind Database

Before proceeding with exercises in which you will perform database backups, in this exercise you will clear any database options that are set for the StudyNwind database.

To clear the database options

Open SQL Server Enterprise Manager.

In the console tree, expand the Databases folder.

Right-click the StudyNwind database icon, and then click Properties.

On the Options tab, clear any selected options.

Click OK.

Exercise: Using SQL Server Enterprise Manager to Back Up the StudyNwind Database

In this exercise, you will perform a complete database backup of the StudyNwind database to the nwA backup device.

To back up a database with SQL Server Enterprise Manager

Switch to SQL Server Enterprise Manager.

In the console tree, expand Databases, right-click the StudyNwind database, point to All Tasks, and then click Backup Database.

In the SQL Server Backup dialog box, click the General tab if it is not already selected.

Ensure that StudyNwind is selected in Database.

In Name, enter the backup set name StudyNwindFull.

In Description, enter the description First backup of StudyNwind.

Under Backup, click Database - Complete.

Under Destination, Disk should be selected. If you do not have a tape drive connected to the computer, Disk and Tape will both be grayed and Disk will be selected; this is correct.

Under Destination, click Add to add the nwA backup device.

In the Choose Backup Destination dialog box, click Backup Device and select nwA from the drop-down list of devices. (If nwA is not in the list, you may already have added it as a destination or you may not yet have created it.)

Click OK to close the dialog box and add nwA to the Backup To list.

If there are any other devices in the Backup To list, highlight them and click Remove to remove them.

Under Overwrite, click the Overwrite Existing Media option to overwrite any existing backups on the backup device.

Click the Options tab.

Check the Verify Backup Upon Completion option, and make sure none of the other options are checked.

Click OK to perform the backup.

Exercise: Using Transact-SQL to Append a Backup to the nwA Device

In this exercise, you will write and execute a Transact-SQL statement to perform another complete database backup of the StudyNwind database and append the backup to the nwA backup device. You will find the script for this exercise in C:\Sqladmin\Exercise\Ch09\Append.sql on your hard disk drive if you have installed the files from the Supplemental Course Materials CD-ROM.

To append a subsequent backup to a backup device with Transact-SQL

Switch to SQL Server Query Analyzer.

Write and execute a Transact-SQL statement that backs up the StudyNwind database and appends the backup to the nwA backup device. Use the options shown in the following table.

Option	Value
Database name	StudyNwind
Backup device	nwA
Append, overwrite, or initialize	Append (WITH NOINIT)
Name	StudyNwindFull2
Description	The second full backup of StudyNwind

Exercise: Viewing the Contents of the Backup Device

In this exercise, you will use SQL Server Enterprise Manager to view the contents of the nwA backup device to ensure that it contains two complete database backups.

To view the contents of the backup device

Switch to SQL Server Enterprise Manager.

In the console tree, expand Management, and click Backup.

In the details pane, right-click the nwA backup device, and then click Properties.

Click View Contents.

Confirm that the backup device contains two complete database backups.

Use Windows NT Explorer to view the C:\Mssql7\Backup folder. Note the size of the nwA.bak file, which is the backup device containing two database backup sets.

Exercise: Using Transact-SQL to Overwrite the Backup

In this exercise, you will write and execute a Transact-SQL statement to back up the StudyNwind database and overwrite any existing backups on the nwA backup device. You will find the script for this exercise in Exercise\Ch09\Overwrit.sql.

To overwrite an existing backup with Transact-SQL

Switch to SQL Server Query Analyzer.

Write and execute a Transact-SQL statement to back up the StudyNwind database onto the nwA backup device. To do so, use the options shown in the following table.

Option	Value
Database name	StudyNwind
Backup device	NwA
Append, overwrite, or initialize	Overwrite (WITH INIT)
Name	StudyNwindFull3
Description	The third full backup of StudyNwind; overwrites the others

Use SQL Server Enterprise Manager, as you did in the previous exercise, to view the contents of nwA and confirm that the other backups were overwritten.

Exercise: Backing Up the StudyNwind Database to Multiple Backup Devices

In this exercise, you will use SQL Server Enterprise Manager to perform a complete database backup of the StudyNwind database onto two existing backup devices: nwstripeA and nwstripeB. You will also overwrite any existing data, including header information.

To back up one database to multiple backup devices

Switch to SQL Server Enterprise Manager.

In the console tree, expand Databases, right-click the StudyNwind database, point to All Tasks, and then click Backup Database.

In the SQL Server Backup dialog box, click the General tab if it is not already selected.

Ensure that StudyNwind is selected in Database.

In Name, enter the backup set name StudyNwind Striped.

In Description, enter the descriptionm A parallel backup of StudyNwind.

Under Backup, click Database - Complete.

Under Destination, Disk should be selected. If you do not have a tape drive connected to the computer, Disk and Tape will both be grayed and Disk will be selected; this is correct.

Under Destination, click Add to add nwstripeA.

In the Choose Backup Destination dialog box, click Backup Device and select nwstripeA from the drop-down list of devices. (If nwstripeA is not in the list, you may already have added it as a destination or you may not yet have created it.)

Click OK to close the dialog box and add nwstripeA to the Backup To list.

Repeat steps 9, 10, and 11 for the nwstripeB device.

If nwA or any other devices appear in the Backup To list, highlight them and click Remove to remove them.

Under Overwrite, click the Overwrite Existing Media option to overwrite any existing backups on the backup device.

Click the Options tab.

Check the Initialize And Label Media option, and make sure that no other options are checked.

Enter nwstripe for the Media Set Name.

Enter Striped backup of StudyNwind for the Media Set Description.

Click OK to perform the backup.

Exercise: Viewing the Contents of the Backup Devices

In this exercise, you will use SQL Server Enterprise Manager to view the contents of the nwstripeA and nwstripeB backup devices to ensure that they contain a striped database backup.

To view the contents of multiple backup devices

Switch to SQL Server Enterprise Manager.

In the console tree, expand Management, and then click Backup.

In the details pane, right-click the nwstripeA backup device, and then click Properties.

Click View Contents.

Confirm that the backup device contains a database backup.

Note the media name and the media sequence of the media.

Repeat steps 3 through 6 for nwstripeB.

Note that both devices are part of the nwstripe media set but that each device contains a different family from the media set. For these disk devices, the media number is always 1; for tape devices, each tape in a family will have a different number.

Use Windows NT Explorer to view the C:\Mssql7\Backup folder. Note the size of the nwstripeA.bak and nwstripeB.bak files, which are the backup devices that each contain part of the striped database backup set.

Exercise: Backing Up the StudyNwind Database to a Temporary Device

In this exercise, you will write and execute a single Transact-SQL statement to back up the StudyNwind database to a new backup device. You will find the script for this exercise in Exercise\Ch09\Bactonew.sql.

To back up a database and create a temporary backup device

Switch to SQL Server Query Analyzer.

Write and execute a single Transact-SQL statement that backs up the StudyNwind database to a new backup device. Use the options shown in the following table.

Option	Value
Database name	StudyNwind
File location	C:\Mssql7\Backup\Mynewbackup.bak
Append, overwrite, or initialize	Initialize (WITH FORMAT)
Name	MyNewBackup
Description	New backup device, not recorded as a named backup device

Start Microsoft Windows NT Explorer.

Expand the C:\Mssql7\Backup folder and examine Mynewbackup.bak to confirm that this backup device was created and populated.

Performing a Transaction Log Backup

You back up transaction logs to record modifications to the database since the last database or transaction log backup. Do not back up a transaction log unless you have performed a complete database backup at least once, because transaction logs cannot be restored without a corresponding database backup.

How SQL Server Backs Up the Transaction Log

When you back up the transaction log, SQL Server does the following:

Backs up the transaction log from the point at which the last successful backup ended to the end of the current transaction log.

Truncates the transaction log up to the beginning of the active portion of the transaction log. The active portion of the transaction log starts at the earlier of the most recent checkpoint or the oldest open transaction and continues to the end of the transaction log.

The syntax for the BACKUP LOG statement is as follows:

 BACKUP LOG {database_name | @database_name_var} TO <backup_device> [, …n]  [WITH      [[,] {INIT | NOINIT}]     [[,] [NAME = {backup_set_name | @backup_set_name_var}] ]

NOTE
The BACKUP LOG statement replaces the DUMP TRANsaction statement found in previous versions of SQL Server. DUMP TRANsaction is still supported but for backward compatibility only. You should use the BACKUP LOG statement.

The following example creates a named backup device and backs up the transaction log of the Northwind database.

 USE master EXEC sp_addumpdevice 'disk', 'nwndbaclog',     'c:\mssql7\backup\nwndbaclog.bak' BACKUP LOG northwind TO nwndbaclog

Exercise: Using SQL Server Enterprise Manager to Back Up the StudyNwind Transaction Log

In this exercise, you will use SQL Server Enterprise Manager to back up the transaction log for the StudyNwind database onto the nwlog backup device.

To back up a transaction log with SQL Server Enterprise Manager

Switch to SQL Server Enterprise Manager.

In the console tree, expand Databases, right-click the StudyNwind database, point to All Tasks, and then click Backup Database.

In the SQL Server Backup dialog box, click the General tab if it is not already selected.

Ensure that StudyNwind is selected in Database.

In Name, enter the backup set name StudyNwindLog.

In Description, enter Transaction log backup of StudyNwind.

Under Backup, click Transaction Log.

Under Destination, Disk should be selected. If you do not have a tape drive connected to the computer, Disk and Tape will both be grayed and Disk will be selected; this is correct.

Under Destination, click Add to add the nwlog backup device.

In the Choose Backup Destination dialog box, click Backup Device and select nwlog from the drop-down list of devices. (If nwlog is not in the list, you may already have added it as a destination or you may not yet have created it.)

Click OK to close the dialog box and add nwlog to the Backup To list.

If there are any other devices in the Backup To list, highlight them and click Remove to remove them.

Under Overwrite, click the Overwrite Existing Media option to overwrite any existing backups on the backup device.

Click OK to perform the backup.

After the backup completes, view the contents of the nwlog backup device with SQL Server Enterprise Manager under Management, Backup.

Exercise: Using Transact-SQL to Append a Transaction Log Backup

In this exercise, you will write and execute a Transact-SQL statement to append a second backup of the transaction log to the nwlog backup device. You will find the script for this exercise in Exercise\Ch09\Appendlg.sql.

To back up a transaction log with Transact-SQL statements

Switch to SQL Server Query Analyzer.

Write and execute a Transact-SQL statement to append a second backup of the transaction log to the nwlog backup device. Use the options shown in the following table.

Option	Value
Database name	StudyNwind
Backup device	nwlog
Append, overwrite, or initialize	Append (WITH NOINIT)

Clearing the Transaction Log

You can use the BACKUP LOG statement with either the TRUNCATE_ONLY option or the NO_LOG option to clear the transaction log. You should back up the transaction log regularly to keep it at a reasonable size. If the transaction log becomes full, users cannot update the database. You must then truncate the transaction log to free up space.

Using the TRUNCATE_ONLY or NO_LOG Option

To truncate the transaction log when it has become full or clear the transaction log without keeping a backup copy of the data, use the BACKUP LOG statement with the TRUNCATE_ONLY or NO_LOG option. SQL Server removes the inactive part of the log without making a backup copy of it. This frees space in the transaction log file. Note the following regarding the use of these options:

You can clear the transaction log before you perform a complete database backup. Doing so may result in a smaller complete database backup.

Once you have cleared the transaction log, you can no longer recover the changes that were recorded in it. You should always execute the BACKUP DATABASE statement immediately after truncating the transaction log using one of these options.

Using these options breaks the transaction log backup sequence. You must perform a database backup before performing further normal transaction log backups.

You cannot use both the TRUNCATE_ONLY option and the NO_LOG option in the same statement.

The TRUNCATE_ONLY and NO_LOG options are synonymous. In previous versions of SQL Server, they had different functions.

The transaction log file will not become smaller after truncation. Space is freed within the file so that database modifications are again possible, but the file itself stays the same size.

To reduce the size of the transaction log file, use the DBCC SHRINKDATABASE statement. Remember that this is a deferred operation and the transaction log file will become smaller only when a certain amount of activity has occurred in the database.

The syntax for the BACKUP LOG statement is as follows:

 BACKUP LOG {database_name | @database_name_var} TO <backup_device> [, …n]  [WITH {TRUNCATE_ONLY | NO_LOG }]

The following example uses the BACKUP LOG statement to remove the inactive portion of a transaction log without making a backup copy.

 BACKUP LOG northwind WITH TRUNCATE_ONLY

Exercise: Using Transact-SQL to Clear the Transaction Log

In this exercise, you will write and execute a Transact-SQL statement to clear the transaction log of the StudyNwind database. Assume that the log has become full. After you clear the transaction log, you must back up the database. You will find the script for this exercise in Exercise\Ch09\Clearlog.sql.

To clear a transaction log without making a backup copy

Write and execute a Transact-SQL statement to clear the transaction log of all committed transactions.

Write and execute a Transact-SQL statement to back up the database to the nwA backup device.

Setting the trunc. log on chkpt. Option

You can set the trunc. log on chkpt. option to true to have SQL Server automatically truncate the transaction log when a checkpoint occurs. Use this option when you want to prevent the transaction log from becoming full, such as when you are working with a test database or if you are using a backup strategy that involves database backups only .

If you set the trunc. log on chkpt. option to true, you cannot back up the transaction log and use it to help restore the database in the event of a system failure. The transaction log no longer stores the changes that have been made to the database since the last database backup.

Using the NO_TRUNCATE Option

If secondary data files that are not part of the primary filegroup are damaged or lost, you should back up the database with the NO_TRUNCATE option. Using this option backs up all recent database activity from the transaction log.

If NO_TRUNCATE is specified, SQL Server does the following:

Backs up the transaction log even if the database is inaccessible

Does not purge the transaction log of committed transactions

Allows you to recover data up to the time when the system failed

When you restore the database, you can restore the database backup and apply the transaction log backup created with the NO_TRUNCATE option to recover data.

IMPORTANT
You can use the NO_TRUNCATE option only if the database's primary data and transaction log files are intact. If you intend to use this option, you should create a secondary file in another filegroup and make this the default filegroup. This will keep the primary data file small and free of user objects. You should also place the primary data and transaction log files on mirrored disks so that they will still be available even if one disk fails.

The following example uses the WITH NO_TRUNCATE clause to back up the transaction log without truncating the inactive part of the log.

 USE master EXEC sp_addumpdevice 'disk', 'nwndnotrunclog',     'c:\mssql7\backup\nwndnotrunclog.bak' BACKUP LOG northwind TO nwndnotrunclog WITH NO_TRUNCATE

Performing a Differential Backup

You should perform a differential backup to minimize the backup and restore time for frequently modified databases. Perform a differential backup only if you have performed a complete database backup.

In a differential backup, SQL Server backs up the parts of the database that have changed since the last complete database backup. Note that subsequent differential backups continue to back up all changes since the last complete backup; differential backups are not incremental.

When performing a differential backup, SQL Server backs up extents rather than individual pages (for a review of extents, see chapter 5). An extent is backed up when the LSN on any page in the extent is greater than the LSN of the last complete database backup.

When you perform a differential backup, consider the following facts and guidelines:

If a certain row in the database has been modified several times since the last complete database backup, the differential backup contains only the last set of values for that row. This is different from a transaction log backup, which contains a history of all changes to that row.

A differential backup minimizes the time required to back up a database because the backup sets are smaller than in complete backups and you do not have to apply a series of transaction logs.

You should establish a naming convention for backup devices that contain differential backups to distinguish them from devices that contain complete database backups.

The syntax for the BACKUP DATABASE statement is as follows:

 BACKUP DATABASE {database_name | @database_name_var} TO <backup_device> [, …n] [WITH      [[,] DIFFERENTIAL] ]

The following example creates a differential backup on a temporary backup device.

 BACKUP DATABASE northwind TO  DISK = 'D:\Mydata\Mydiffbackup.bak' WITH DIFFERENTIAL

Exercise: Using SQL Server Enterprise Manager to Perform a Differential Backup of the StudyNwind Database

In this exercise, you will perform a differential backup of the StudyNwind database and append the differential backup to the nwdiff.bak backup device. You will find the script for this exercise in Exercise\Ch09\Diffbac.sql.

To perform a differential backup with SQL Server Enterprise Manager

Switch to SQL Server Enterprise Manager.

In the console tree, expand Databases, right-click the StudyNwind database, point to All Tasks, and then click Backup Database.

In the SQL Server Backup dialog box, click the General tab if it is not already selected.

Ensure that StudyNwind is selected in Database.

In Name, enter the backup set name StudyNwindDiff.

In Description, enter the description Differential backup of StudyNwind.

Under Backup, click Database - Differential.

Under Destination, Disk should be selected. If you do not have a tape drive connected to the computer, Disk and Tape will both be grayed and Disk will be selected; this is correct.

Under Destination, click Add to add the backup device.

In the Choose Backup Destination dialog box, click File Name and enter c:\mssql7\backup\nwdiff.bak as the filename.

Click OK to close the dialog box and add the device to the Backup To list.

If there are any other devices in the Backup To list, highlight them and click Remove to remove them.

Under Overwrite, click the Overwrite Existing Media option to overwrite any existing backups on the backup device.

Click OK to perform the backup.

After the backup operation completes, use Windows NT Explorer to confirm that the new backup device is now in the C:\Mssql7\Backup folder.

Performing Data File or Filegroup Backups

Perform data file or filegroup backups on very large databases (VLDB) or when it is not feasible to make a complete database backup due to time constraints. Filegroups contain one or more data files. When SQL Server backs up files or filegroups, it does the following:

Backs up only the data files that you specify with the FILE or FILEGROUP option.

Allows you to back up specific data files instead of the entire database.

When you perform data file or filegroup backups,

You must specify the logical file or filegroup names.

You must perform transaction log backups in order to make restored files consistent with the rest of the database. For this reason, file or filegroup backups are not possible if the trunc. log on chkpt. database option is set to true.

You should establish a plan to back up each file or filegroup in a database on a rotating basis. This is necessary to ensure that all data files or filegroups are backed up regularly.

You do not need to create different file or filegroup backups on different backup devices, although you may want to create different named backup devices to make it easier to manage file and filegroup backups.

The syntax for making filegroup backups with the BACKUP DATABASE statement is as follows:

 BACKUP DATABASE database_name     [<file_or_filegroup> [, …n]] TO <backup_device> [, …n]] where <file_or_filegroup> is {FILE = logical_file_name | FILEGROUP = logical_filegroup_name }

The following example performs a backup of the Orders2 file, followed by a transaction log backup. The phoneorders database has three data files: Orders1, Orders2, and Orders3. The transaction log is stored in the Orderlog file. These backup devices already exist: Orderbackup1, Orderbackup2, Orderbackup3, and Orderbackuplog.

 BACKUP DATABASE phoneorders FILE = orders2 TO orderbackup2 BACKUP LOG phoneorders to orderbackuplog

If an index has been created on a table in a filegroup, the entire filegroup must be backed up. Files in the filegroup cannot be backed up individually. Furthermore, if indexes are created on filegroups other than the filegroup in which their base tables exist, the filegroups must all be backed up together. This can cause the need to back up multiple filegroups together, so you should plan your index and backup requirements carefully when designing your indexes and filegroups. (See Figure 9.2.)

click to view at full size.

Figure 9.2 Scenarios illustrating filegroup backup restrictions

Lesson Summary

Backup operations can be performed with SQL Server Enterprise Manager, the Backup Wizard, or Transact-SQL. You should be familiar with certain backup options whenever you use any of the SQL Server backup methods.

A differential backup minimizes the backup and restore time for frequently modified databases. Perform a differential backup only if you have performed a complete database backup previously.