Piecemeal Restore in SQL Server

A piecemeal restore sequence restores and recovers a database in stages at the file group level, beginning with the primary and all read-write, secondary file groups. In this process, we can restore the primary file group first making database online and the remaining file groups can be restored while recovery while the transactions are running on primary. Mostly suitable for data warehousing databases.

Consider we have a database of 4 TB whereas on primary file group is a read write FILEGROUP of size 1 TB and we have other files groups which are read-only of size 3 TB. In such cases, it becomes difficult task to perform full database backup and restore as it may take several hours to complete the backup and restore operation. 

Piecemeal restore process allows us to restore the primary FILEGROUP first and the database can be online and the remaining FILEGROUPS can be restored while the recovery the transactions are running on primary File group. Mostly suitable for data warehousing databases.

Demo


In this demo, we will see exactly how to perform piecemeal restore on a sample database.


-- If exist drop database
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PiecemealDB')
   DROP DATABASE [PiecemealDB]
GO

--Create database PiecemealDB
Create database PiecemealDB

--Add filegroup ReadOnlyFG to the the database
ALTER DATABASE PiecemealDB
ADD FILEGROUP ReadOnlyFG;
GO

--Add datafile to the filegroup ReadOnlyFG
ALTER DATABASE PiecemealDB
ADD FILE
(
    NAME = ReadOnlyFile,
    FILENAME = 'E:\Samir\ReadOnlyData.ndf',
    SIZE = 10 MB,
    MAXSIZE = 100 MB,
    FILEGROWTH = 15 MB
)
TO FILEGROUP ReadOnlyFG;
GO

--Verify the filegroups using below T-SQL
Use [PiecemealDB]
GO
SELECT DB_NAME() DatabaseName,
dbf.name FileName,
size/128 SizeMB,
fg.name FGName,dbf.physical_name
FROM sys.database_files dbf INNER JOIN sys.filegroups fg 
ON dbf.data_space_id=fg.data_space_id
GO


--Add a table to the filegroup
CREATE TABLE PrimaryTbl
      (
            EmpID int IDENTITY(1,1) PRIMARY KEY,
            EmpName varchar(100) NOT NULL,
            FileGroupType varchar(100)
      )
GO
CREATE TABLE ReadOnlyTbl
      (
            EmpID int IDENTITY(1,1) PRIMARY KEY,
            EmpName varchar(100) NOT NULL,
            FileGroupType varchar(100)
      )
      ON ReadOnlyFG;
GO

--Insert some records in a table
INSERT INTO PrimaryTbl (EmpName,FileGroupType)
values('John','PrimaryFG'),('John','PrimaryFG'),('John','PrimaryFG'),('John','PrimaryFG'),('John','PrimaryFG')
GO
INSERT INTO ReadOnlyTbl (EmpName,FileGroupType)
values('Sam','ReadOnlyFG'),('Sam','ReadOnlyFG'),('Sam','ReadOnlyFG'),('Sam','ReadOnlyFG'),('Sam','ReadOnlyFG')

--Verify the data inserted into a table
Select * from PrimaryTbl;
Select * from ReadOnlyTbl;


--Change Filegroup status to readonly.
use master
GO
ALTER DATABASE PieceMealDB MODIFY FILEGROUP ReadOnlyFG READ_ONLY;

--Verify the filegroup status by executing following T-SQL
USE [PiecemealDB]
GO
SELECT DB_NAME() databasename,dbf.name [FileName],
         size/128 SizeMB,fg.name FGName,dbf.physical_name,
         dbf.state_desc,dbf.is_read_only
FROM
      sys.database_files dbf INNER JOIN sys.filegroups fg
ON dbf.data_space_id=fg.data_space_id
GO


--Perform full database backup
BACKUP DATABASE PiecemealDB
    TO DISK = 'F:\DBBackup\PieceMealDB_Full.bak'
    WITH FORMAT;
GO

--Perform backup for read-only filegroup
BACKUP DATABASE PiecemealDB
   FILEGROUP = 'ReadOnlyFG'
   TO DISK = 'F:\DBBackup\ReadOnlyFG.bak'
WITH FORMAT
GO


--Drop the database to perform the piecemeal recovery process of the database
USE MASTER;
GO
DROP DATABASE [PiecemealDB]

--The RESTORE DATABASE command with PARTIAL clause starts a new piecemeal restore operation. The keyword PARTIAL indicates that the restore process involves a partial restore. The partial keyword defines and initiates the partial-restore sequence. This will be validated during the recovery stages.

--Now, restore read-write filegroups using READ_WRITE_FILEGROUPS clause.
USE MASTER
GO
RESTORE DATABASE PiecemealDB READ_WRITE_FILEGROUPS
      FROM DISK = 'F:\DBBackup\PieceMealDB_Full.bak'  
WITH PARTIAL, RECOVERY 
GO

--Verify the output by querying the read-only table data
SELECT *
      FROM [PiecemealDB].[dbo].[PrimaryTbl]
GO
SELECT *
      FROM [PiecemealDB].[dbo].[ReadOnlyTbl]


--Restore ReadOnly Filegroup
USE [master]
GO
RESTORE DATABASE PiecemealDB
   FILE = 'ReadOnlyFile',
   FILEGROUP = 'ReadOnlyFG'
   FROM DISK = 'F:\DBBackup\ReadOnlyFG.bak'
   WITH RECOVERY


--Verify the output by querying the read-only table data
SELECT * FROM PrimaryTbl
GO
SELECT * FROM ReadOnlyTbl



No comments:

Post a Comment