MICROSOFT SQL SERVER: 2009

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

Partitioned Table

A partitioned table is a table in which the data is separated horizontally into multiple physical locations based on a range of values for a specific column. The physical locations for partitions are filegroups. For example, you could use a partitioned table to store sales orders and then separate the order records into different filegroups based on
the order date.

Benefits of Partitioned Tables
• The ability to implement separate backup strategies.

• Control over storage media.
• Index management benefits.

Partitioned Function
A partition function specifies the data type of the key used to partition the data and the
boundary values for each partition. The number of partitions defined by a partition
function is always one more than the number of boundary values that the function defines.
For example, a partition function that defines a Integer partitioning key with the
boundary values 1, 2, and 3’ will result in four partitions <1,1-2,2-3,>3.
Example:-
CREATE PARTITION FUNCTION pf_OrderDate (datetime)
AS RANGE RIGHT
FOR VALUES ('01/01/2003', '01/01/2004', '01/01/2005')

Partitioned Function
A partition scheme maps the partitions that are defined in a partition function to the
filegroups where the partitions will be physically stored. You can map all partitions to the
same filegroup, or you can map some or all of the partitions to different filegroups,
depending on your needs.

Example:-
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (fg1, fg2, fg3, fg4, fg5)
---------------------------------------------------------------------------------
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
ALL TO ([PRIMARY])
Fg5 is next used partition will be used in future with partition table.

Example Partitioned Tables
CREATE TABLE TESTEMPLOYEE
(EMPID INT IDENTITY ,EMPNAME CHAR(40),RETDATE DATETIME,RETMONTH AS CONVERT(TINYINT,DATEPART (MM,RETDATE)) PERSISTED
CONSTRAINT PKKEY PRIMARY KEY (EMPID,RetMONTH))
ON MyPartitionScheme(RETMONTH)

Switching Partitions
You can swap a populated table or partition with an empty table or partition by using the
SWITCH clause of the ALTER TABLE statement. This technique is commonly used to
archive data from a partitioned table or to bulk-insert new data from a staging table.
ALTER TABLE dbo.PartitionedTransactions
SWITCH PARTITION 1
TO dbo.TransactionArchive

Merging Partitions
You can use the ALTER PARTITION FUNCTION statement to merge a partition. When
you perform a merge operation, the partition for the boundary value specified in the
ALTER PARTITION FUNCTION statement is removed, and the data is merged into the
adjacent partition.
ALTER PARTITION FUNCTION pf_OrderDate()
MERGE RANGE ('01/01/2003')

Splitting Partitions
Like a merge, a split operation is performed by using the ALTER PARTITION
FUNCTION statement. This creates a new partition and reassigns the data accordingly.
The new partition is created on the filegroup designated as the next filegroup in each
partition scheme based on the partition function. If a partition scheme has no next
filegroup defined when the partition is split, an error occurs. So earlier fg5 next used partition is used by this function.
ALTER PARTITION FUNCTION pf_OrderDate()
SPLIT RANGE ('01/01/2006')

Designing Partitions Table To Archive Data In Another Database At The End Of Each Month Automatically.

--Create Database for Partition table with 12 filegroups and Data file associated with each

CREATE DATABASE [PartitionDB] ON PRIMARY
( NAME = N'PartitionDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB.mdf' , SIZE = 4048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup1]
( NAME = N'PartitionDB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB1.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup10]
( NAME = N'PartitionDB10', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB10.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup11]
( NAME = N'PartitionDB11', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB11.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup12]
( NAME = N'PartitionDB12', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB12.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup2]
( NAME = N'PartitionDB2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB2.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup3]
( NAME = N'PartitionDB3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB3.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup4]
( NAME = N'PartitionDB4', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB4.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup5]
( NAME = N'PartitionDB5', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB5.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup6]
( NAME = N'PartitionDB6', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB6.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup7]
( NAME = N'PartitionDB7', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB7.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup8]
( NAME = N'PartitionDB8', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB8.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ),
FILEGROUP [filegroup9]
( NAME = N'PartitionDB9', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB9.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'PartitionDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\PartitionDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Sample Picture is Given

--Use Database
use PartitionDB
go
--create partition function to create logical boundary for each month
CREATE PARTITION FUNCTION MyPartitionRange (TINYINT)
AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11)
GO
--create partition scheme to associate in logical boundary with file groups
CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION MyPartitionRange
To([filegroup1],[filegroup2],[filegroup3],[filegroup4],[filegroup5],[filegroup6],[filegroup7],[filegroup8],[filegroup9],[filegroup10],[filegroup11],[filegroup12])
GO
--now create a table with store data using partition scheme and partition function
--see we have created computed column RETMONTH to idetified the month of employee retirment and pass this to partition scheme. make sure if you are associate a computed column with partition schema it must be persisted.
CREATE TABLE EMPLOYEE
(EMPID INT IDENTITY ,EMPNAME CHAR(40),RETDATE DATETIME,RETMONTH AS CONVERT(TINYINT,DATEPART (MM,RETDATE)) PERSISTED
CONSTRAINT PKKEY PRIMARY KEY (EMPID,RetMONTH))
ON MyPartitionScheme(RETMONTH)
Go
--populate partition table with data.(adventure works sample database is reuired for [Person].[Contact] table.
INSERT INTO EMPLOYEE
SELECT [FirstName]
,[ModifiedDate]
FROM [AdventureWorks].[Person].[Contact]
go
--varify no of records in each partition by using sys.partition function
SELECT partition_number,rows
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('EMPLOYEE')
go
--varify rows are related to which partition for this we are usinf $partition predicate with partition function
SELECT *,$PARTITION.MyPartitionRange(retmonth)as PartitionNo FROM EMPLOYEE
go
--ARCHIVED Destination
--creating Database for archived table
Create database ArchivedDB
Go
Use ArchivedDB
go
-- Create table to store Archived data.
CREATE TABLE ArchivedData
(EMPID INT ,EMPNAME CHAR(40),RETDATE DATETIME )
Go
--varify there is no records in ArchivedData
SELECT * FROM ArchivedData
go
--we are creating temprory table to switch data using StoreProc to transfer data from source to temprory table and then archicedtable
--Considrations
--1. Switch can transfer data to empty table or empty partition only.-
--2. Destination(Empty Table or Partition) must be exits on same filegroup from where data is extracted.
--3.you can not create a temp table becouse it exists in TEMDB.
--4.Temp table must have same schema as source table even charter length of columns.
-- we are using dynamic sql to craete temp table on same filegroup from data is extracted by passing filegroup as parameter.
GO
USE PartitionDB
GO
CREATE PROC SWITCHTABLE @FG CHAR(20)
AS
BEGIN
exec ('CREATE TABLE TempEmployee

(EMPID INT IDENTITY ,EMPNAME CHAR(40),RETDATE DATETIME,RETMONTH AS CONVERT(TINYINT,DATEPART (MM,RETDATE)) PERSISTED
CONSTRAINT PKKEYTEMP PRIMARY KEY (EMPID,RETMONTH))
ON '+@FG)
END
Go
-- Creating another storeproc to swith data from source to temp table created earlier we have to pass partition id from data gets extracted passing partition id as parameter.
create proc SWAPPARTITION @ID INT
AS
BEGIN
EXEC ('ALTER TABLE EMPLOYEE switch PARTITION
'+' '+@ID+' TO TempEmployee')
END
go
--another proc to transfer data from temp table to destination archived table in archivedDB
CREATE PROC ARCHIVEDATA
AS
BEGIN
INSERT INTO ArchivedDB.dbo.ArchivedData SELECT EMPID,EMPNAME,RETDATE FROM TempEmployee
END
go
-- create single store proc to call all three proc(creaete table,swith partionand transfer data)
--ones you get partitionid you can extract the file group associtead with it so we are using a query to know archived partition file group and pass this to first procedure to create temp table after archiving we have to make sure that temp table must be drop for next time.
CREATE PROC SWAPEMPLOYEE @ID INT
AS
BEGIN
Declare @fg Char(20)
select @fg=fg.Name from sys.indexes i inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id
left outer join sys.partition_range_values prv on prv.boundary_id = dds.destination_id
where i.[object_id] = object_id('Employee') and destination_id =@id
EXEC SWITCHTABLE @FG
EXEC SWAPPARTITION @ID
EXEC ARCHIVEDATA
DROP TABLE TempEmployee
END
Go

--create a t-sql job and schedule at the end on each month with given code.
go
use PartitionDB
go
DECLARE @PNO INT
SET @PNO=DATEPART(MM,GETDATE())
EXEC SWAPEMPLOYEE @PNO
go