Piecemeal Restoration in SQL Server 2005

How to Perform a Piecemeal Restore

SQL Server 2005 Enterprise Edition supports piecemeal restore operations, which enable you to recover one or more entire filegroups while the remaining filegroups are online. Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model Piecemeal restore allows filegroups to be restored after an initial, partial restore of the primary and some of the secondary filegroups. Filegroups that are not restored are marked as offline and are not accessible. The offline filegroups, however, can be restored later by performing a file restore. To allow the entire database to be restored in stages at different times, piecemeal restore maintains checks to ensure that the database will be
consistent in the end.

Performing a Piecemeal Restore
A piecemeal restore sequence restores and recovers a database in stages at the filegroup
level, beginning with the primary and all read/write, secondary filegroups.
--Start the Example by connecting to sql server
USE [master]
Go
--create database for demo make sure that database comtains multiple filegroups.
CREATE DATABASE [TestDB] ON PRIMARY
( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' , SIZE = 4048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [fg1] ( NAME = N'fg1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\fg1.ndf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [fg2] ( NAME = N'fg2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\fg2.ndf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [fg3]
( NAME = N'fg3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\fg3.ndf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON
( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
Go
/* create multiple backup device for for database,filegroup and log backup*/
--device for filegroup1 backup
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'fg1', @physicalname = N'C:\fg1.bak'
Go
--device for filegroup2 backup
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'fg2', @physicalname = N'C:\fg2.bak'
Go
--device for filegroup3 backup
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'fg3', @physicalname = N'C:\fg3.bak'
Go
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'prm', --device for Primary backup
@physicalname = N'C:\prm.bak'
Go
--device for TransactionLog1 backup
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N't1', @physicalname = N'C:\t1.bak'
Go
--device for TransactionLog2 backup
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N't2', @physicalname = N'C:\t2.bak'
Go
--device for TransactionLog3 backup
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N't3', @physicalname = N'C:\t3.bak'
Go
--device for TransactionLog Tail backup
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'tail', @physicalname = N'C:\tail.bak'
Go
--device for Full database backup
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'full', @physicalname = N'C:\full.bak'
Go

-- use database
USE testdb
Go
--create table located to each filegroup and insert one row in each
--Table for filegroup1
CREATE TABLE T1
(ID INT)
ON FG1
Go
INSERT INTO T1 VALUES(1)
Go
--Table for filegroup1
CREATE TABLE T2
(ID INT)
ON FG2
Go
INSERT INTO T2 VALUES(1)
Go
--Table for filegroup3
CREATE TABLE T3
(ID INT)
ON FG3
Go
INSERT INTO T3 VALUES(1)
Go
--Table for Primary FileGroup
CREATE TABLE T4
(ID INT)
ON [PRIMARY]
Go
INSERT INTO T4 VALUES(1)
Go
----take full backup and file group backup of Database
BACKUP DATABASE [TestDB] TO [full] WITH NOFORMAT, NOINIT, NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Go
BACKUP DATABASE [TestDB] FILEGROUP = N'PRIMARY' TO [prm] WITH NOFORMAT, NOINIT, NAME = N'TestDB-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Go
BACKUP DATABASE [TestDB] FILEGROUP = N'fg1' TO [fg1] WITH NOFORMAT, NOINIT, NAME = N'TestDB-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Go
BACKUP DATABASE [TestDB] FILEGROUP = N'fg2' TO [fg2] WITH NOFORMAT, NOINIT, NAME = N'TestDB-Full Filegroup Backup1', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Go
BACKUP DATABASE [TestDB] FILEGROUP = N'fg3' TO [fg3] WITH NOFORMAT, NOINIT, NAME = N'TestDB-Full Filegroup Backup2', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--insert few more rows before Transation log backup(it is optional)
--before transaction log1
INSERT INTO T1 VALUES(2)
INSERT INTO T2 VALUES(2)
INSERT INTO T3 VALUES(2)
INSERT INTO T4 VALUES(2)
--take transaction backup
--tranasction log backup taken as normal schedule
BACKUP LOG [TestDB] TO [t1] WITH NOFORMAT, NOINIT, NAME = N'TestDB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Go
--insert few more rows before Transation log backup(it is optional)
--before transaction log2
INSERT INTO T1 VALUES(3)
INSERT INTO T2 VALUES(3)
INSERT INTO T3 VALUES(3)
INSERT INTO T4 VALUES(3)
Go
--take transaction backup2
--tranasction log backup taken as normal schedule
BACKUP LOG [TestDB] TO [t2] WITH NOFORMAT, NOINIT, NAME = N'TestDB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Go
--insert few more rows before Transation log backup(it is optional)
--before transaction log3
INSERT INTO T1 VALUES(4)
INSERT INTO T2 VALUES(4)
INSERT INTO T3 VALUES(4)
INSERT INTO T4 VALUES(4)
Go
--tranasction log backup taken as normal schedule
--take transaction backup2
BACKUP LOG [TestDB] TO [t3] WITH NOFORMAT, NOINIT, NAME = N'TestDB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Go
--before transaction TAIL
INSERT INTO T1 VALUES(5)
INSERT INTO T2 VALUES(5)
INSERT INTO T3 VALUES(5)
INSERT INTO T4 VALUES(5)
Go
--Now ur database is crash and you are manage to get tail log bakup of your database
--Tail log backup before restoration Process
--TAKE TAIL LOG
Use master
go
BACKUP LOG [TestDB] TO [tail] WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'TestDB-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10
Go


--Now restoration process is get started from here after ecah phase of restoration
--you will see that respective filegroups will come online for processing and other are still unaccessible.
-----RESTORATION PROCESS for primary and filegroup1
--this always start with Primary Filegroup and follwed by other
RESTORE DATABASE TestDB FILEGROUP='Primary' FROM prm
WITH PARTIAL, NORECOVERY
Go
RESTORE DATABASE TestDB FILEGROUP='fg1' FROM fg1
WITH NORECOVERY
Go
RESTORE LOG TestDB FROM t1 WITH NORECOVERY
Go
RESTORE LOG TestDB FROM t2 WITH NORECOVERY
Go
RESTORE LOG TestDB FROM t3 WITH NORECOVERY
Go
RESTORE LOG TestDB FROM tail WITH RECOVERY
Go
--check sataus of filegroup accesiblity
-- you will see here that table belongs to primary and filegroup1 are accesible and other are not.
use TestDB
Go
--accesible filegroup1
select * from t1
Go
--not accesible filegroup2(show you error unable to show becouse table exits on file filegroup which is not online)
Select * from t2
Go
--not accesible filegroup3(show you error unable to show becouse table exits on file filegroup which is not online)
select * from t3
Go
--accesible Primary filegroup
select * from t4
Go
--after some time you can start filegroup2 restoration
--RESTORATION PROCESS for filegroup2
use master
Go
RESTORE DATABASE TestDB FILEGROUP='fg2' FROM fg2
WITH NORECOVERY
Go
RESTORE LOG TestDB FROM t1 WITH NORECOVERY
Go
RESTORE LOG TestDB FROM t2 WITH NORECOVERY
Go
RESTORE LOG TestDB FROM t3 WITH NORECOVERY
Go
RESTORE LOG TestDB FROM tail WITH RECOVERY
Go

--check the accesiblty now.
use TestDB
Go
--accesible filegroup1
select * from t1
Go
--accesible filegroup2
select * from t2
Go
--not accesible filegroup3(show you error unable to show becouse table exits on file filegroup which is not online)
select * from t3
Go
--accesible Primary filegroup
select * from t4
Go
--at the end restore filegroup3 and full database will become online.
--restoration for filegroup3
Use master
Go
RESTORE DATABASE TestDB FILEGROUP='fg3' FROM fg3
WITH NORECOVERY
Go
RESTORE LOG TestDB FROM t1 WITH NORECOVERY
Go
RESTORE LOG TestDB FROM t2 WITH NORECOVERY
Go
RESTORE LOG TestDB FROM t3 WITH NORECOVERY
Go
RESTORE LOG TestDB FROM tail WITH RECOVERY
Go
--check the accesiblty now.
use TestDB
Go
--accesible filegroup1
select * from t1
Go
--accesible filegroup2
select * from t2
Go
--accesible filegroup3
select * from t3
Go
--accesible Primary filegroup
select * from t4
Go
--Clean your server
use master
go
drop database [TestDB]
Go
EXEC sp_dropdevice 'fg1'
EXEC sp_dropdevice 'fg2'
EXEC sp_dropdevice 'fg3'
EXEC sp_dropdevice 't1'
EXEC sp_dropdevice 't2'
EXEC sp_dropdevice 't3'
EXEC sp_dropdevice 'prm'
EXEC sp_dropdevice 'tail'
EXEC sp_dropdevice 'full'
Go