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