Piecemeal Restore

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'PiecemealDemo')
   DROP DATABASE [PiecemealDemo]
GO

--Create database PiecemealDemo

Create database PiecemealDemo

--Add filegroup ReadOnlyFG to the the database

ALTER DATABASE PiecemealDemo
ADD FILEGROUP ReadOnlyFG;
GO

--Add datafile to the filegroup ReadOnlyFG

ALTER DATABASE PiecemealDemo
ADD FILE
(
    NAME = ReadOnlyFile,
    FILENAME = 'E:\Anurag\ReadOnlyData.ndf',
    SIZE = 10 MB,
    MAXSIZE = 100 MB,
    FILEGROWTH = 15 MB
)
TO FILEGROUP ReadOnlyFG;
GO

--Verify the filegroups using below T-SQL

Use [PiecemealDemo]
GO

SELECT DB_NAME() DatabaseName,
dbf.name FileName,
size/128 SizeMB,
fg.name FGName,dbf.physical_name
FROM sys.database_files dbf INNERJOIN sys.filegroups fg 
ON dbf.data_space_id=fg.data_space_id

GO

--Add a table to the filegroup

CREATE TABLE PrimaryTable
      (
            EmpID int IDENTITY(1,1) PRIMARY KEY,
            EmpName varchar(100) NOT NULL,
            FileGroupType varchar(100)
      )
GO
CREATE TABLE ReadOnlyTable
      (
            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 PrimaryTable(EmpName,FileGroupType)
values('John','PrimaryFG'),('John','PrimaryFG'),('John','PrimaryFG'),('John','PrimaryFG'),('John','PrimaryFG')
GO
INSERT INTO ReadOnlyTable(EmpName,FileGroupType)
values('Anurag','ReadOnlyFG'),('Anurag','ReadOnlyFG'),('Anurag','ReadOnlyFG'),('Anurag','ReadOnlyFG'),('Anurag','ReadOnlyFG')

--Verify the data inserted into a table

Select * from PrimaryTable;
Select * from ReadOnlyTable;

--Change Filegroup status to readonly.

use master
GO
ALTER DATABASE PiecemealDemo MODIFYFILEGROUP ReadOnlyFG READ_ONLY;

--Verify the filegroup status by executing following T-SQL

USE [PiecemealDemo]
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 JOINsys.filegroups fg
ON dbf.data_space_id=fg.data_space_id

--Perform full database backup

BACKUP DATABASE PiecemealDemo
    TO DISK = 'F:\DBBackup\PiecemealDemo_Full.bak'
    WITH Compression, stats=1;
GO

--Perform backup for read-only filegroup

BACKUP DATABASE PiecemealDemo
   FILEGROUP = 'ReadOnlyFG'
   TO DISK = 'F:\DBBackup\ReadOnlyFG.bak'
WITH Compression, stats=1;

--Drop the database to perform the piecemeal recovery process of the database

USE MASTER;
GO

DROP DATABASE [PiecemealDemo]

--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 PiecemealDemo READ_WRITE_FILEGROUPS
      FROM DISK ='F:\DBBackup\PiecemealDemo_Full.bak'  
WITH PARTIAL, RECOVERY 
GO

--Verify the output by querying the read-only table data

SELECT *
      FROM [PiecemealDemo].[dbo].[PrimaryTable]
GO
SELECT *
      FROM [PiecemealDemo].[dbo].[ReadOnlyTable]

Restore ReadOnly Filegroup

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

--Verify the output by querying the read-only table data

SELECT * FROM PrimaryTable
GO
SELECT * FROM ReadOnlyTable