SQL Server Magazine

A frequently asked question on the SQL Server public help forums is, "How can I recover just one of the tables in my database?" The need for single-table recovery can arise if someone issues an inappropriate UPDATE or DELETE statement that changes a substantial amount of the table's data in unintended ways. In this situation, an administrator might want to replace the data in that table with data that was previously backed up. Another time you might want to restore only a subset of the data in the database is when one of the disks that contain your SQL Server database fails but the other disks are still fully functional. If you have a database backup from before the unintended change or disk failure took place, one recovery strategy is to restore the entire database into a new location, then copy the appropriate data from the restored database into the original database. However, for very large databases (VLDBs) that contain tens or hundreds of gigabytes of data, the time and space requirements might make this solution impractical. Having SQL Server restore the data that belongs to one specific table or set of tables isn't as easy as it might seem. However, SQL Server 6.5 and later releases give you some options for recovering only parts of a database.

Single-Table 6.5
SQL Server 6.5's mechanism for restoring data from one table is the LOAD TABLE command. The source of the table data can be a backup file of just that table, which you create by using the DUMP TABLE command, or a complete database backup. This single-table restore capability isn't as useful as it sounds, however. To make a backup of one table, you have to anticipate the need for restoring that table. If your database consists of hundreds of tables, you need to decide which tables are most likely to need individual recovery. Or rather than trying to decide which tables to back up, you might decide to make individual backups of every table. But the time, space, and administrative resources required to manage these backups can make this technique even less practical than just restoring the entire database.

Alternatively, when you're using SQL Server 6.5, you could restore the table from a complete database backup. This solution sounds ideal: You probably have complete database backups, so you don't need any extra time, space, or administrative resources for the backup operations. However, this solution has a big downside. Table data in a database backup file won't necessarily be transactionally consistent, so if you restore just the data from a single table, that data might be inconsistent and reflect an in-progress transaction.

For example, suppose that while a database backup operation is running, you issue the following transaction in a much larger version of the Pubs database:

UPDATE titles
SET price = price * 0.80

This statement attempts to update the price of every title in the titles table with a price decrease of 20 percent. The backup operation in SQL Server 6.5 attempts to back up each page in page-number order, but the pages in a table aren't necessarily in any particular sequence. Let's say that the titles table occupies pages 100 to 200 and pages 800 to 1000 in the database. The backup operation might write pages 100 to 200 to the backup device before the UPDATE statement starts, so these pages would keep the original prices. Then, while the backup thread is writing pages 201 to 799, SQL Server updates the entire table according to the above statement. So by the time the backup thread writes pages 800 to 1000, those pages contain the new, updated data values. The titles table in the full backup is now transactionally inconsistent. If someone performs a LOAD TABLE operation for the titles table, some rows will contain the original price values and some rows will contain updated values. Thus, SQL Server 6.5 can't guarantee that transactions are atomic—that either all the changes happen or none of them happen.

However, if no one performs a LOAD TABLE operation, the inconsistent table data isn't a problem because the full database backup also contains part of the transaction log. If you restore the entire database (rather than one table), SQL Server will run the same recovery process that it runs every time it starts up. During recovery, SQL Server compares the transactions in the log with the data in the database. The recovery process will detect that some pages in the titles table aren't up-to-date and will roll forward the changes to the rows on pages 100 to 200. When the recovery process is complete and the database is fully restored, the titles table will be transactionally consistent and all the rows will reflect the values that the UPDATE statement changed. Because of these shortcomings in SQL Server 6.5's ability to back up and restore a single table, SQL Server 2000 and 7.0 don't have this capability. In these releases, Microsoft included the capability of backing up and restoring files and filegroups.

Backing up and Restoring Files and Filegroups
When you create a database in SQL Server 2000 and 7.0, you can choose to spread the data over multiple files. You can also create named filegroups, which contain one or more files. (For details about creating and managing databases with multiple files or filegroups, see Michael D. Reilly, Certifiably SQL, "Creating SQL Server 7.0 Databases," November 1999, and Wayne Snyder, "Ensuring Up-to-the-Minute Database Recovery," October 1999.) One reason you might want to use multiple files and named filegroups is that these options let you create a table within a particular filegroup. You can't specify which file to create the table in, but you can create a filegroup with just one file. Placing a table in that filegroup is equivalent to placing a table in the only file that filegroup contains.

   Prev. page   [1] 2 3     next page