How to restore backup by piece meal

Quoted from http://blogs.msdn.com/b/sqlcat/archive/2005/10/27/how-can-sql-server-2005-online-piecemeal-restore-improve-availability.aspx

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:

Primary filegroup	FGCustomers (RW)	FGOrders (RW)	FGOrderHistory (RO)
Contains system tables only, no user tables	Customers Customers data	Orders & OrderDetails 2005 Orders partition         OrderDetails	OrderHistory 2000-2004 Orders partitions

Customers

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.

File_id	Name	Physical_name	State	State_Desc
1	ProdDB_data	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBPri1dat.mdf	0	ONLINE
2	SProdDB_log	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBlog.ldf	0	ONLINE
3	FGCustomers_file1	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProdDBCustFi1dt.ndf	3	RECOVERY_PENDING
4	FGOrders_file1	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBOrdFi1dt.ndf	0	ONLINE
5	FGOrderHistory_file1	C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProdDBOrdHistFi1dt.ndf	3	RECOVERY_PENDING

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

----------------------------------------------------------------------------------------------------------------------

The following scripts are quoted from http://stackoverflow.com/questions/25841822/backup-and-restore-sql-server-database-filegroup

The scripts will:-

1. Create a test database, with multiple filegroups which some are read only.
2. Create a development database from a backup of the test database.
3. Take filegroup backups of the test database and restore over the development database

Please have a look and run through the scripts. Let me know if you have any questions.

Before you use the scripts, just make sure that you have the following filepaths on your computer:-

C:\SQLServer\Data
C:\SQLServer\Logs
C:\SQLServer\Backups

The version of SQL Server I use is 2012 SP2 CU2 Developer Edition.

First create the test database:-

CREATE DATABASE [FGRestoreTEST]
 ON  PRIMARY 
( NAME = N'FGRestoreTEST', FILENAME = N'C:\SQLServer\Data\FGRestoreTEST.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2010] 
( NAME = N'FG2010', FILENAME = N'C:\SQLServer\Data\FG2010.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2011] 
( NAME = N'FG2011', FILENAME = N'C:\SQLServer\Data\FG2011.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2012] 
( NAME = N'FG2012', FILENAME = N'C:\SQLServer\Data\FG2012.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2013] 
( NAME = N'FG2013', FILENAME = N'C:\SQLServer\Data\FG2013.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2014] 
( NAME = N'FG2014', FILENAME = N'C:\SQLServer\Data\FG2014.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'FGRestoreTEST_log', FILENAME = N'C:\SQLServer\Logs\FGRestoreTEST_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Then create tables in each of the filegroups:-

USE [FGRestoreTEST];
GO

CREATE TABLE [PRIMARY_TABLE]
(ID INT,
 NAME CHAR(4)) ON [PRIMARY];

CREATE TABLE [FG2010_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2010];

 CREATE TABLE [FG2011_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2011];

CREATE TABLE [FG2012_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2012];

CREATE TABLE [FG2013_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2013];

CREATE TABLE [FG2014_TABLE]
(ID INT,
 NAME CHAR(4)) ON [FG2014];
 GO

Insert data (100 rows) into each of the tables:-

INSERT INTO [PRIMARY_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2010_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2011_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2012_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2013_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2014_TABLE]
SELECT 1, 'TEST'
GO 100

Then set certain filegroups to read only:-

ALTER DATABASE [FGRestoreTEST]
MODIFY FILEGROUP [FG2010] READ_ONLY;

ALTER DATABASE [FGRestoreTEST]
MODIFY FILEGROUP [FG2011] READ_ONLY;

ALTER DATABASE [FGRestoreTEST]
MODIFY FILEGROUP [FG2012] READ_ONLY;

ALTER DATABASE [FGRestoreTEST]
MODIFY FILEGROUP [FG2013] READ_ONLY;
GO

Take a full backup:-

USE [master];
GO

BACKUP DATABASE [FGRestoreTEST]
TO DISK = N'C:\SQLServer\Backups\FGRestoreTEST.BAK';
GO

Then create a development database from the full backup (this will be used to restore the filegroup backups that will be taken further next):-

RESTORE DATABASE [FGRestoreTEST_Dev]
FROM DISK = N'C:\SQLServer\Backups\FGRestoreTEST.BAK' WITH
MOVE 'FGRestoreTEST' TO 'C:\SQLServer\Data\FGRestoreTEST_Dev.mdf',
MOVE 'FG2010' TO 'C:\SQLServer\Data\FG2010_Dev.ndf',
MOVE 'FG2011' TO 'C:\SQLServer\Data\FG2011_Dev.ndf',
MOVE 'FG2012' TO 'C:\SQLServer\Data\FG2012_Dev.ndf',
MOVE 'FG2013' TO 'C:\SQLServer\Data\FG2013_Dev.ndf',
MOVE 'FG2014' TO 'C:\SQLServer\Data\FG2014_Dev.ndf',
MOVE 'FGRestoreTEST_log' TO 'C:\SQLServer\Logs\FGRestoreTEST_Dev_log.ldf',
RECOVERY,STATS=5;
GO

Take backups of each of the filegroups:-

--http://msdn.microsoft.com/en-us/library/ms189906.aspx
BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'PRIMARY'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_PRIMARY.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2010'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2010.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2011'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2011.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2012'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2012.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2013'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_G2013.bak';

BACKUP DATABASE [FGRestoreTEST]
   FILEGROUP = 'FG2014'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2014.bak';
GO

Now we will modify data in the Primary & FG2014 filegroups:-

USE [FGRestoreTEST];
GO

INSERT INTO [PRIMARY_TABLE]
SELECT 1, 'TEST'
GO 100

TRUNCATE TABLE [FG2014_TABLE];
GO

Take differential backups of the filegroups:-

BACKUP DATABASE [FGRestoreTest]
   FILEGROUP = 'PRIMARY'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTest_PRIMARYDIFF.bak'
   WITH DIFFERENTIAL;

BACKUP DATABASE [FGRestoreTest]
   FILEGROUP = 'FG2014'
   TO DISK = 'C:\SQLServer\Backups\FGRestoreTest_FG2014DIFF.bak'
   WITH DIFFERENTIAL;
GO

Again, modify the data:-

USE [FGRestoreTEST];
GO

INSERT INTO [PRIMARY_TABLE]
SELECT 1, 'TEST'
GO 100

INSERT INTO [FG2014_TABLE]
SELECT 1, 'NEW'
GO 300

Backup the transaction log (you will probably have more than one of these in a real life environment, but for demo purposes I'm just taking one):-

USE [master];
GO

BACKUP LOG [FGRestoreTEST]
TO DISK = 'C:\SQLServer\Backups\FGRestoreTest_LogBackup.trn';
GO

OK, now we can restore the development database. First we take a Tail Log backup, putting the database into recovery. Note:- we will not use this backup!

BACKUP LOG [FGRestoreTEST_Dev]
TO DISK = 'C:\SQLServer\Backups\FGRestoreTest_TailLogBackup.trn'
WITH NORECOVERY;
GO

Now we can restore the full backups of the read-write filegroups:-

--http://msdn.microsoft.com/en-us/library/aa337540.aspx
--Restore primary filegroup
RESTORE DATABASE [FGRestoreTEST_Dev]
   FILEGROUP = 'PRIMARY'
   FROM DISK = 'C:\SQLServer\Backups\FGRestoreTEST_PRIMARY.bak'
   WITH NORECOVERY;
GO

--Restore FG2014 filegroup 
RESTORE DATABASE [FGRestoreTEST_Dev]
   FILEGROUP = 'FG2014'
   FROM DISK = 'C:\SQLServer\Backups\FGRestoreTEST_FG2014.bak'
   WITH NORECOVERY;
GO

Then the differential backups:-

--Restore PRIMARY differential backup
RESTORE DATABASE [FGRestoreTEST_Dev]
   FILEGROUP = 'PRIMARY'
   FROM DISK = 'C:\SQLServer\Backups\FGRestoreTest_PRIMARYDIFF.bak'
   WITH NORECOVERY;
GO

--Restore FG2014 differential backup
RESTORE DATABASE [FGRestoreTEST_Dev]
   FILEGROUP = 'FG2014'
   FROM DISK = 'C:\SQLServer\Backups\FGRestoreTest_FG2014DIFF.bak'
   WITH NORECOVERY;
GO

Then the transaction log backup:-

RESTORE LOG [FGRestoreTEST_Dev]
FROM DISK = 'C:\SQLServer\Backups\FGRestoreTest_LogBackup.trn'
WITH NORECOVERY;
GO

Finally, the database can be recovered:-

RESTORE DATABASE [FGRestoreTest_DEV] WITH RECOVERY;
GO

And as a final test, check the data:-

USE [FGRestoreTEST_Dev];
GO

SELECT COUNT(*) AS [PRIMARY_TABLE]
FROM [PRIMARY_TABLE];

SELECT COUNT(*) AS [FG2010_TABLE]
FROM [FG2010_TABLE];

SELECT COUNT(*) AS [FG2011_TABLE]
FROM [FG2011_TABLE];

SELECT COUNT(*) AS [FG2012_TABLE]
FROM [FG2012_TABLE];

SELECT COUNT(*) AS [FG2013_TABLE]
FROM [FG2013_TABLE];

SELECT COUNT(*) AS [FG2014_TABLE]
FROM [FG2014_TABLE];

SELECT TOP (1) *
FROM [FG2014_TABLE];
GO

So, from the data changes made, we would expect to see 300 records in the PRIMARY & FG2014 filegroups, 100 in the rest and the all the values in the name column in the FG2014 set as 'NEW'.