Partial or Piecemeal Database Restore in MS SQL Server

Problem Statement:

These days data is growing rapidly and maintaining its availability as per defined RTO & RPO has become biggest challenge for a DBA. This blog post will help you to design the recovery of such database.

Suppose, we have a database over 10TB, which is a combination of transactional and historical data (not a good design I know). The severity of the processes running on transactional data are high and cannot be taken down for more than 1 hour. Historical data is only being used by reports which can wait in case of disaster.

Recovery Solutions: 

Physical partition of database: 

You can physically move historical data into different database on different drives. This will reduce the main database size and can help to meet desired RTO and RPO of transactional and historical databases. This solution is quite complex in terms of implementation and maintenance as you would need to keep your historical data updated.

SQL Server Table Partitioning on different Filegroups and piecemeal restore:

What is table partitioning: Table partitioning is a way to divide a large table into smaller, more manageable parts without having to create separate tables for each part. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. 

How this will solve our problem:  We can distribute historical and transactional data into different filegroups using table partitioning and use piecemeal restore feature to recover transactional data first so that all the dependent process can access the data. We would need to design the database to make use of this piecemeal restoration, this will involve categorization of filegroups as per their severity.

I will only cover 2nd option that is database recovery with piecemeal restore as this is a preferred option. In this example, we have a database with multiple filegroups and table partitioned across the filegroups: (I will cover table partitioning in details later in another blog post)

Database name: PiecemealRestoreDB

• Primary filegroup (default) – If we have designed this database keeping piecemeal restore in mind then we need to keep Primary filegroup as small as we can.
• Filegroup_C – This data needs to be recovered first as most critical processes are dependent on the data stored in this filegroup
• Filegroup_B – After recovering FILEGROUP_C, this data needs to be recovered as less business critical processes are dependent on the data stored in FILEGROUP_B
• Filegroup_A – This filegroup only contains historical data and can be recovered later as reports running on top of this data are not business critical and can wait until recovery process has been completed.

-- CREATE PARTITION FUNCTION & SCHEME
USE PieceMealRestoreDB
GO
CREATE PARTITION FUNCTION pf_Emp_Joining_date (DATETIME) AS RANGE RIGHT FOR VALUES('2018-01-01 00:00:00', '2020-01-01 00:00:00')
CREATE PARTITION SCHEME ps_Emp_Joining_date AS PARTITION pf_Emp_Joining_date TO ([FILEGROUP_A], [FILEGROUP_B], [FILEGROUP_C])
GO
-- CREATE TABLE
CREATE TABLE dbo.EmployeePartitionedTable
    (
      emp_id INT NOT NULL ,
      emp_JoiningDate DATETIME NOT NULL ,
      Emp_name VARCHAR(50) NOT NULL ,
      CONSTRAINT pk_empid_joiningDate PRIMARY KEY CLUSTERED
        ( emp_id, emp_JoiningDate )
    )
ON  ps_Emp_Joining_date(emp_JoiningDate)
GO
 
-- INSERT TEST DATA
INSERT  INTO dbo.EmployeePartitionedTable
        SELECT  1 ,
                '2017-01-02 00:00:00' ,
                'Peter'
INSERT  INTO dbo.EmployeePartitionedTable
        SELECT  2 ,
                '2018-01-02 00:00:00' ,
                'John'
INSERT  INTO dbo.EmployeePartitionedTable
        SELECT  3 ,
                '2019-01-02 00:00:00' ,
                'Sam'
INSERT  INTO dbo.EmployeePartitionedTable
        SELECT  4 ,
                '2020-01-02 00:00:00' ,
                'Smith'
GO

Here is a SQL command which shows us the details of data stored within it partitions.

SELECT  * ,
        $partition.pf_Emp_Joining_date(emp_JoiningDate) AS Partition_order
FROM    dbo.EmployeePartitionedTable
GO

Now data is distributed as per plan so let’s move onto next part i.e. backup and restore.

Database backups are scheduled as below:

• Full Database backup: Weekly on Sunday
• Differential backups: Daily differential except Sunday
• Log Backups: every 15 minutes

Let’s take a Full Backup of the database now:

BACKUP DATABASE [PieceMealRestoreDB] TO 
 DISK = N'G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PieceMealRestoreDB.bak' 
 WITH   NAME = N'PieceMealRestoreDB-Full Database Backup'
 GO

Let’s insert few more rows into partitioned table:

INSERT  INTO dbo.EmployeePartitionedTable
        SELECT  5 ,
                '2020-01-03 00:00:00' ,
                'Sam'
INSERT  INTO dbo.EmployeePartitionedTable
        SELECT  6 ,
                '2020-02-02 00:00:00' ,
                'Smith' 
GO

Take differential backup of the database now:

BACKUP DATABASE [PieceMealRestoreDB] TO  DISK = N'G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PieceMealRestoreDB_Diff.bak' 
WITH  DIFFERENTIAL , 
NAME = N'PieceMealRestoreDB-Differntial Database Backup'
GO

Insert few more rows into partitioned table:

INSERT  INTO dbo.EmployeePartitionedTable
        SELECT  7 ,
                '2020-01-21 00:00:00' ,
                'Rocky'
INSERT  INTO dbo.EmployeePartitionedTable
        SELECT  8 ,
                '2020-03-01 00:00:00' ,
                'Matt' 
GO

Take Log backup now:

BACKUP LOG [PieceMealRestoreDB] TO  DISK = N'G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PieceMealRestoreDB_log.trn'
 WITH  NAME = N'PieceMealRestoreDB-Log Database Backup'
GO

 

Now, we would need to replicate the scenario that the database is crashed or accidentally dropped, and you cannot access the database. We would need to restore the database partially in following sequence:

Database Recovery:

• Restore database partially from last Full backup (only PRIMARY and FILEGROUP_C)
• Restore database from latest Differential Backup
• Restore log backups after differential backup

Restore Full Database backup to recover PRIMARY and FILEGROUP_C filegroups:

RESTORE DATABASE [PieceMealRestoreDB] FILEGROUP = 'Primary', 
FILEGROUP = 'FILEGROUP_C' 
FROM DISK = N'G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PieceMealRestoreDB.bak' 
WITH NORECOVERY, 
PARTIAL;
GO

Output message shows, the datafiles belong to filegroups PRIMARY and FILEGROUP_C are recovered only.

Restore Differential backup to apply changes made after Full backup:

RESTORE DATABASE [PieceMealRestoreDB] 
FROM DISK = N'G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PieceMealRestoreDB_Diff.bak' 
WITH NORECOVERY;
GO

Again recovered only PRIMARY and FILEGROUP_C

We can check the state of each file using query mentioned as below

SELECT
'DB_NAME' = db.name,
'FILE_NAME' = mf.name,
'FILE_TYPE' = mf.type_desc,
'FILE_PATH' = mf.physical_name, 
mf.STATE_DESC
FROM
sys.databases db
INNER JOIN sys.master_files mf
ON db.database_id = mf.database_id WHERE db.name='PieceMealRestoreDB'

Output shows that intended filegroups are in Restoring mode and rest are recovery_pending.

Let’s restore the log backup (do not run both the scripts together, if first fails then second script will bring the database in recovery mode):

RESTORE LOG [PieceMealRestoreDB] 
FROM DISK = N'G:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PieceMealRestoreDB_Log.trn' 
WITH NORECOVERY;
GO
RESTORE LOG [PieceMealRestoreDB] 
WITH RECOVERY;
GO

Please see the output as below:

Check the state of each file again with the script mentioned earlier:

Now we have recovered our most critical data and less critical data is still not recovered which can be recovered in the background using similar process.

Let’s have a look at recovered data:

It failed as the query plan wanted to SCAN the index and it could not access the other filegroups as FILEGROUP_A and FILEGROUP_B are offline. As per the error message SQL cannot be certain it retrieved all the data the query asked for and so it must also report the error “This may limit the query result” .

This is not a surprise, this is how SQL works so in this crisis situation where you bring your database online partially which means recovering the most critical data before all data is ready, thus your application must account for this error and should use filters as per latest data defined in your partition functions.

If we use appropriate filter and design the processes as per database design, this piecemeal restore can solve your big recovery problem.

You can restore remaining filegroups as per their priority later using same process.

please leave a comment if you have any feedback or question.