SQL Server Magazine

After creating and populating the testdb database, you can use the following command to back it up:

BACKUP DATABASE testdb TO DISK = 'c:\backup\testdb.bak'
WITH INIT

Now suppose that someone unintentionally or maliciously destroys Table1. You can perform a partial restore of testdb by creating a copy of it that contains only filegroup FG1, which contains Table1, and the primary filegroup and log. Listing 2 shows a command to perform a partial restore of testdb into a new database called testdb_partial. To run this code, change the physical filenames or paths as appropriate for your system configuration.

You can now use the sp_help system stored procedure to examine testdb_partial; note that the database shows both a Table1 and a Table2. You can select from Table1 and see the data, but when you select from Table2, you get the error 8653 shown earlier.

Variations on a Theme
Let's look at a couple of other situations involving partial backup. What does SQL Server restore if the backup you're using was made in the middle of a transaction involving multiple rows from Table1? And what's restored if the backup was made after a completed transaction involving both Table1 and Table2?

To simulate the first situation, you need to use Query Analyzer to run a transaction in multiple batches by highlighting and executing only a few statements at a time. Because SQL Server won't let you execute a backup command from a session that's in the middle of a transaction, you need to use another connection window to run a backup command. So in one query window, execute the beginning of a transaction:

BEGIN TRAN
INSERT INTO table1
  SELECT 'This is before the backup'
GO

And in a second connection window, back up the testdb database, using the INIT option to overwrite the previous backup:

BACKUP DATABASE testdb
TO DISK = 'c:\backup\testdb.bak'
WITH INIT
GO

After the backup is complete, return to the first connection window and complete the transaction:

INSERT INTO table1
  SELECT 'This is after the backup'
COMMIT TRAN
GO

Now let's look at what the backup captured. Make sure you have no connections to the testdb_partial database, then rerun the script in Listing 2 to recreate testdb_partial. If you select the data from Table1, you'll see only the original row:

With SQL Server 2000 backup and restore, you're not left with inconsistent data. Because the transaction was in progress when the backup occurred, only part of the transaction was reflected in the restored database. However, the restore process applied the portion of the transaction log that you backed up along with the database backup. This log restore operation undid any changes that you hadn't committed when the backup was made, so the table returns to its original state.

You can use the script in Listing 3 to simulate the situation in which the backup was made after a completed transaction involving both Table1 and Table2. The script drops testdb_partial and recreates the tables in FG1 and FG2 in the original testdb database. The code in Listing 4 shows a transaction that spans Table1 and Table2, which are on two different filegroups. The transaction commits, then the script starts the backup. Let's assume that sometime after this backup, someone damages or modifies the data in Table1 and you want to return Table1 to its condition at the last database backup. To get back the data, you can use the script in Listing 2 to restore testdb_partial.

When you examine the data in Table1, note that it contains the new row you inserted during the transaction in Listing 4, but Table2, which had a row inserted during the same transaction, is completely unavailable. You're performing the partial restore because Table1 on filegroup FG1 was unavailable or damaged, so the only important thing to restore is Table1. You can now copy the data in Table1 from testdb_partial into the original testdb, where Table2 still exists with the data that the code in Listing 4 changed. After you perform the partial restore and copy the data from the partially restored database to the original, the original testdb now has both Table1 and Table2 in a consistent state—both reflecting the changes from the committed transaction in Listing 4.

Being able to restore only a subset of the files or filegroups into an existing database helps you recover from media failure without the time and space requirements of restoring an entire database. And the ability to partially restore a database into a new location, then copy that data to your original database, helps you efficiently recover from user errors.

End of Article

Prev. page     1 2 [3]     next page -->