How can SQL Server 2005 OnLine Piecemeal Restore improve availability?

Question 1: How can SQL Server 2005 OnLine Piecemeal Restore improve availability?

Question 2: Can I recover a single object such as a table or specific partition(s) from a partitioned table?

Answer:

The question is how to get the greatest flexibility, manageability, availability and recoverability for large databases. Anytime you need to restore a database to a specific point-in-time, all filegroups in that database must be restored to the same point-in-time. Restoring an entire multi-terabyte database to a point-in-time could result in a big outage, an especially severe penalty if the data to be restored is relatively small compared to the entire database. While SQL Server 2000 provided filegroup restores, all filegroups must be available for the database to be accessible.

To provide greater flexibility for large DB scenarios, a new feature of SQL Server 2005 called OnLine Piecemeal Restore allows us to use specific filegroup backups to create a smaller work database containing only the data we wish to restore. By placing tables (including individual partitions of partitioned tables) on specific filegroups, we can devise a granular backup and restore strategy where part of a database such as a table or even a single partition of a partitioned table, can be restored quickly. We can also roll transactions forward to a specific point-in-time with transaction logs using any of the following: a specific time, a transaction mark, or a log sequence number (LSN).

The example database, ProdDB has the following secondary filegroups:

· Customers: FGCustomers

· Current year orders (2005): FGOrders

· Historical orders (2000-2004): FGOrderHistory.

ProdDB contains three large user tables: Customers, Orders and OrderDetails. The Customers table is placed on filegroup FGCustomers. The table Orders is partitioned by year. The current years’ orders (2005) are placed on FGOrders, while years 2000-2004 are placed on filegroup FGOrderHistory. The layout of ProdDB is as follows:

The combination of filegroup backups and rolling transaction logs forward provide recoverability to a point-in-time. Assume that a batch job has resulted in incorrect 2005 data and that you must perform a partial filegroup restore of FGOrders (shaded in gray) to recover the data.

Steps for partial online filegroup restore to a point-in-time:

1. Backup ProdDB’s filegroups primary, FGCustomers, FGOrders, FGOrderHistory

2. Batch jobs run (assume batch job messes up 2005 data……)

3. Backup tail of transaction log (assuming you need to roll forward to point-in-time in the log)

4. Perform piecemeal restore. Use ProdDB primary & specific filegroup (FGOrders) backup to create a new work database called PartialDB. Roll forward transaction log to point in time (just before bad batch job). The 2005 partition of Orders was recovered from the FGOrders backup.

5. Delete bad data in ProdDB.

6. Copy restored partition or table from work database PartialDB to ProdDB

Example of filegroup & transaction log backups, bad batch job, and online piecemeal restore

1. Backup ProdDB filegroups & transaction log:

BACKUP DATABASE ProdDB

FILEGROUP = 'PRIMARY'

to disk = 'C:\ProdDB_FG_PRIMARY_SQL.BAK' with init

-- SQL FGCustomers FILEGROUP backup

BACKUP DATABASE ProdDB

FILEGROUP = 'FGCustomers'

to disk = 'C:\ProdDB_FG_FGCustomers_SQL.BAK' with init

-- SQL FGOrders FILEGROUP backup

BACKUP DATABASE ProdDB

FILEGROUP = 'FGOrders'

to disk = 'C:\ProdDB_FG_FGOrders_SQL.BAK' with init

-- SQL FGOrderHistory FILEGROUP backup

BACKUP DATABASE ProdDB

FILEGROUP = 'FGOrderHistory'

to disk = 'C:\ProdDB_FG_FGOrderHistory_SQL.BAK' with init

-- SQL log backup

BACKUP log ProdDB

to disk = 'C:\ProdDB_LOG_SQL1.BAK' with init

2. Assume a batch job messes up some 2005 data

-- assume a batch job wrongly updates or deletes some 2005 data

DELETE from [ProdDB]..Orders

where OrderID in (select top 50 OrderID from [ProdDB]..Orders where OrderDate > '2005-01-30')

3. Backup tail of log:

-- Backup tail of transaction log (assuming you need to roll forward to point-in-time in the log)

BACKUP log ProdDB

to disk = 'C:\ProdDB_LOG_SQL2.BAK' with init

4. OnLine Piecemeal Restore creates work database PartialDB:

-- PartialDB will be created from ProdDB backups.

-- MOVE used to create files for PartialDB

RESTORE DATABASE PartialDB

from disk = 'C:\ProdDB_FG_PRIMARY_SQL.BAK'

WITH NORECOVERY, REPLACE, PARTIAL – partial:not all FG will be restored

,MOVE 'ProdDB_data' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBPri1dat.mdf'

,MOVE 'SProdDB_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBlog.ldf'

-- restore only the latest 2005 data, oldest partitions on FGOrdersHistory not restored

RESTORE DATABASE PartialDB

from disk = 'C:\ProdDB_FG_FGOrders_SQL.BAK'

WITH NORECOVERY

      ,MOVE 'FGOrders_file1' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBOrdFi1dt.ndf'

-- restore first log up to point-in-time. You can also restore to a transaction mark or LSN.

RESTORE log PartialDB

from disk = 'C:\ProdDB_LOG_SQL1.BAK'

 WITH NORECOVERY,  STOPAT = 'Oct 26, 2005 11:00 PM'  

-- restore second log up to point-in-time. You can also restore to a transaction mark or LSN.

RESTORE log PartialDB

from disk = 'C:\ProdDB_LOG_SQL2.BAK'

 WITH RECOVERY,  STOPAT = 'Oct 26, 2005 11:00 PM'  

-- NOTE: Piecemeal restore. Part of PartialDB is recovered & online.

select file_id, name, physical_name, state, state_desc

from sys.database_files

Note if you choose to use a transaction mark to establish the point-in-time to recover to, remember that the transaction must be writable, e.g. it must contain an insert, update, or delete within the transaction for the transaction mark to be written to the log. A transaction mark with select statements only is not a writeable transaction.

Note that the newly created work database PartialDB contains only 3 files from 2 filegroups. A backup of the Primary filegroup restores the files ProdDB_data (contains system catalog tables) and SProdDB_log which is the transaction log. The secondary filegroup FGOrders is used to recreate FGOrders_file1. FGOrders contains only the 2005 Orders data.

PartialDB special notes:

The primary filegroup contains the system tables that define objects for the database. As a best practice and to expedite the restore process, it is suggested that user objects are placed on secondary filegroups, not the primary filegroup. Once the primary filegroup is restored, the system tables contain the metadata for all objects in the database, although you may not be actually restoring all objects.

While you can select data from any ONLINE filegroup, remember that some objects such as a partitioned table, can span filegroups. If some but not all partitions of a partitioned table (such as in the example above) have been recovered, an unqualified SELECT * FROM Orders, or any other object that has not been recovered will likewise fail as follows:

Msg 679, Level 16, State 1, Line 1

One of the partitions of index '' for table 'dbo.Orders'(partition ID 72057594038452224) resides on a filegroup that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

5. Delete bad data in ProdDB.

Before restoring the data in ProdDB , you must remove the bad data in ProdDB using various techniques such as

a. drop table (if you plan on using select into to recreate the object)

b. truncate table to remove all rows or delete to remove specific rows using a WHERE clause

c. switch out (for partitioned tables). A partition must be empty (using switch out) before you can switch the new data in. Once switched out, you can drop the bad data.

6. Copy partition or table from PartialDB to ProdDB.

Use copy techniques to restore the data in ProdDB. Recreate indexes on the copied object, add any constraints, etc.  Some copy alternatives to consider:

a. select * into ProdDB..OrderDetails from PartialDB..OrderDetails (if you are restoring the entire table, drop table in ProdDB first)

b. insert into ProdDB..Orders select * from PartialDB..Orders where OrderDate >= '2005-01-30'

c. create work table on correct filegroup, copy data, switch in (for partitioned tables). Note: The definition of the work source table and target partitioned table must match exactly. The target partition must be empty. The source table (used in the switch in) must be on the same filegroup as the partition.

If you restore an object to a specific point-in-time, you may have to consider the effects on referential integrity (RI). You may have to perform RI checks to ensure relationships are intact.

Summary:

In conclusion, SQL Server 2005 provides a flexible filegroup backup strategy that should be especially helpful in large database scenarios. By placing tables (including individual partitions of partitioned tables) on specific filegroups, we can devise a granular backup and restore strategy where we can restore part of a database quickly to a smaller work database. Using the techniques described above, we can recover entire tables or specific table partitions. Combined with transaction marks, we can restore to a specific point-in-time. After the object has been restored to a work database, the last step is to copy it to the production database. After the copy, the work database can be dropped.

Tom Davidson

SQL Server Customer Advisory Team