When your database is on multiple files or filegroups, SQL Server 2000 and 7.0 let you back up and restore individual files or filegroups. This ability can be useful with extremely large databases. You can choose to back up just one file or filegroup each day so that you don't need to back up the entire database as often. The ability to back up and restore individual files or filegroups can also be useful when you have an isolated failure on one drive and restoring the entire database would take too long. Even if you've only done full database backups, you can restore individual filegroups from a full backup and replace the damaged file or filegroup with the backed-up copy.
With this capability to restore a single filegroup, you might think you'd have the option of restoring only one table if that table existed on its own filegroup. Suppose you created filegroup FG1 containing one file, File1, then you created one table called Table1 on that filegroup. If someone incorrectly modifies Table1 and you have a full database backup from just before the modification, you might try to restore just the filegroup containing Table1, hoping to bring back the old data in Table1. However, this approach won't work. After restoring a file or filegroup backup, you must also restore all the transaction logs made between the last time you backed up the file or filegroup and the time you restored it. This requirement is supposed to guarantee that the restored files are in sync with the rest of the database. But in this case, you don't want the restored files to be in sync with the rest of the database, because applying all the transaction-log backups will redo all the unintentional modifications to Table1 that you're trying to undo.
Although restoring a file or filegroup isn't a solution for recovering a table damaged by user error, it helps with the problem of isolated media failure. For example, suppose you back up filegroup FG1 at 10:00 a.m. Monday. As users access the database, changes happen to the data on FG1 and SQL Server processes transactions that change data in FG1 and other filegroups. You back up the log at 4:00 p.m., and SQL Server processes more transactions that change data in FG1 and other filegroups. But at 6:00 p.m., a media failure occurs and you lose one or more of the files that make up FG1.
To restore, you must first back up the tail of the log that contains all changes that occurred between 4:00 p.m. and 6:00 p.m. You can then restore filegroup FG1 by using the RESTORE DATABASE command, specifying just filegroup FG1. Your database won't be in a consistent state because the restored FG1 will have changes only through 10:00 a.m., but the rest of the database will have changes through 6:00 p.m. However, SQL Server knows when the last change was made to the database because each page in a database contains information about when it was last modified. When you restore a filegroup, SQL Server makes a note of the latest time any page in the database changed. You must then restore log backups until the log reaches at least that latest change time; in this example, you'll reach that point when you apply the 6:00 p.m. log backup that you made after the failure occurred.
Partial Restore
SQL Server 2000 adds to the restore operation another capability that lets you do a partial restore of a database in special situations. In SQL Server 2000 Books Online (BOL), the description and syntax for a partial restore seem similar to those for file and filegroup backups, but partial restore differs in one significant way. With file and filegroup backups, you start with a complete database and replace one or more files or filegroups with previously backed-up versions. With a partial database restore, you don't start with a full database. You restore individual filegroups—including the primary filegroup, which contains all the system tables—to a new location, essentially creating a new database containing a subset of the original database. Any unrestored filegroups don't exist in the new database, and SQL Server treats them as OFFLINE when you attempt to reference data stored on them. You can then restore log backups or differential backups to update the data in those filegroups with recent changes.
This partial restore process gives you the option of recovering the data from a subset of tables after an accidental deletion or modification of table data. You can use the partially restored database to extract the data from the lost or damaged table and copy it back into your original database.
When performing a partial restore, you must always restore the primary filegroup, which contains all the system tables. When you restore the primary filegroup, all system tables are restored, even those associated with files that aren't included in the restore operation. So sp_help or Enterprise Manager will show the tables because they exist in the sysobjects system table, but when you try to access a table on an unrestored filegroup, you'll get a message like this one:
Server: Msg 8653, Level 16, State 1, Line 1
Warning: The query processor is unable to produce a plan because
the table 'table2' is marked OFFLINE.
Let's look at an example of a partial restore. The operation is intended for use when your databases are too big to restore in their entirety, but for this example, let's create a very small database called testdb. You can run the code that Listing 1 shows to create a database on three filegroups: one primary filegroup and two user-defined filegroups, FG1 and FG2. The code in Listing 1 also creates two tables, one on each user-defined filegroup, and inserts a row of data into each table. You might want to change the physical paths for the files to reflect where your SQL Server 2000 instance is installed or to correspond with your disk configuration.
Prev. page
1
[2]
3
next page 
