Day 27 of 31 Days of Disaster Recovery: Restoring Part of a Database

Today is day 27 of my series 31 Days of Disaster Recovery, and I want to talk about restoring a partial database to a server. If you have a very large database with many filegroups, and you need to restore just part of the database, then you can perform a partial restore (Enterprise Edition required) to only restore minimum amount of filegroups online to get access to the part you need. A good use case for this would be if you need to restore data from backup to recover data that was accidentally deleted.

If you missed any of the earlier posts in my DR series, you can check them out here:

31 Days of disaster Recovery
1. Does DBCC Automatically Use Existing Snapshot?
2. Protection From Restoring a Backup of a Contained Database
3. Determining Files to Restore Database
4. Back That Thang Up
5. Dealing With Corruption in a Nonclustered Index
6. Dealing With Corruption in Allocation Pages
7. Writing SLAs for Disaster Recover
8. Resolutions for All DBAs
9. Use All the Checksums
10. Monitoring for Corruption Errors
11. Converting LSN Formats
12. Extreme Disaster Recovery Training
13. Standard Backup Scripts
14. Fixing a Corrupt Tempdb
15. Running DBCC CheckTable in Parallel Jobs
16. Disaster Recovery Gems From Around The Net
17. When are Checksums Written to a Page
18. How to CHECKDB Like a Boss
19. How Much Log Can a Backup Log
20. The Case of the Backups That Wouldn’t Restore
21. Who Deleted That Data?
22. Which DBCC CHECK Commands Update Last Known Good DBCC
23. Restoring Differential Backups With New Files
24. Handling Corruption in a Clustered Index
25. Improving Performance of Backups and Restores
26. The Mysterious Case of the Long Backup

Partial Restores

As I already stated, partial restores require Enterprise Edition. The way it works is that the database is brought online when the primary filegroup is restored. Then as each successive filegroup is brought online, those filegroups come online as well. This can be very handy for disaster recovery if you have critical data that users need right away in separate filegroups from historical data that is not critical to get online right away. if a user attempts to query any objects in filegroups that are online, the query proceeds as normal. If they attempt to query something in a filegroup that is not online, the query will fail.

For this demo, I’m going to create a new database with 2 additional filegroups. Each filegroup will contain a file which in turn contains a table. One of the filegroups will be marked as read-only and the other will remain read/write.

Use master;
Go

-- Create Database
Create Database TestPiecemealRestores;
Go

-- Make sure recovery is full
Alter Database TestPiecemealRestores Set Recovery Full;
Go

-- Add first filegroup
Alter Database TestPiecemealRestores 
    Add FileGroup SecondaryFG;
Go

-- Add third filegroup
Alter Database TestPiecemealRestores 
    Add FileGroup TertiaryFG;
Go

-- Add file for first table
Alter Database TestPiecemealRestores 
    Add File (
        Name = N'SecondaryFile',
        FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\DATA\TestPiecemealRestores_Secondary.ndf')
    TO FileGroup SecondaryFG;
GO

-- Add file for second table
Alter Database TestPiecemealRestores 
    Add File (
        Name = N'TertiaryFile',
        FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL12\MSSQL\DATA\TestPiecemealRestores_Tertiary.ndf')
    TO FileGroup TertiaryFG;
GO

-- Switch to new database
Use TestPiecemealRestores;
go

-- Create table in FG #2
Create Table dbo.SecondaryFGData (
    DataId int identity(1, 1) Not Null primary key,
    DatabaseID int not null,
    DBName sysname not null,
    FileID int not null)
    On SecondaryFG;
Go

-- Insert data into dbo.SecondaryFGData
Insert Into dbo.SecondaryFGData (DatabaseID, DBName, FileID)
Select database_id,
    DB_NAME(database_id),
    file_id
From sys.master_files;
Go

-- Create table in FG #3
Create Table dbo.TertiaryFGData (
    DataId int identity(1, 1) Not Null primary key,
    LoginID int not null,
    LoginName sysname not null,
    LoginType char(1) not null)
    On TertiaryFG;
Go

-- Insert data into dbo.TertiaryFGData
Insert Into dbo.TertiaryFGData (LoginID, LoginName, LoginType)
Select principal_id,
    name,
    type
From sys.server_principals;
Go

Next, I’m going to set the third filegroup read-only, take a full backup (as best practice after setting filegroup read-only), add more data the second filegroup, and then finally, back up the read/write filegroups in the database using the Read_Write_Filegroups option for the backup command.

-- Switch to master
Use master;
Go

-- Change TertiaryFG filegroup to read-only
Alter Database TestPiecemealRestores
    Modify FileGroup TertiaryFG Read_Only;
Go

-- Back up the full database
Backup Database TestPiecemealRestores
    To Disk = 'd:\backup\TestPiecemealRestores.bak'
    With init;
Go

-- Switch back to database
Use TestPiecemealRestores;
Go

--Insert more data into dbo.SecondaryFGData
Insert Into dbo.SecondaryFGData (DatabaseID, DBName, FileID)
Select database_id,
    DB_NAME(database_id),
    file_id
From sys.master_files;
Go

-- Back up the Read\Write filegroups (primary and SecondaryFG)
Backup Database TestPiecemealRestores
    Read_Write_Filegroups
    To Disk = 'd:\backup\TestPiecemealRestores_RW.bak'
    With init;
Go

No to move on to the restores on a different instance as we have everything we need. I am going to start of by restoring just the read/write filegroups from the read/write filegroups backup that I created. For the demo, I am going to use the Read_Write_Filegroups option for the restore command even though it really is optional in this example. Since the backup I am using contains only read/write filegroups, I don’t need to tell it to restore them. With the option, SQL will restore everything in the backup file. However, if I had chosen to restore from the full backup I took first, I would have had to specify the Read_Write_Filegroups option to avoid restoring the whole database.

That’s an important fact to note. You don’t have to perform piecemeal backups in to perform a piecemeal restore. You can pick and choose what you want to restore from any given backup file.

Since my SQL instances are on the same machine, I do not to specify the MOVE option for all files being restored. The primary filegroup is always the first to be restored and must be included in the restore. Additionally, the log file will be restored as well. This means we are restoring 3 files and need to specify a new location for all 3.

Use master;
Go

-- Restore backup creating a new database. Restore
-- only primary filegroup, SecondaryFG filegroup,
-- and log file.
Restore Database TestPiecemealRestores
    Read_Write_Filegroups
    From Disk = 'd:\backup\TestPiecemealRestores_RW.bak'
    With Move 'TestPiecemealRestores' To 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL13\MSSQL\DATA\TestPiecemealRestores.mdf',
        Move 'TestPiecemealRestores_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL13\MSSQL\DATA\TestPiecemealRestores_log.ldf',
        Move 'SecondaryFile' To 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL13\MSSQL\DATA\TestPiecemealRestores_Secondary.ndf';
Go

We can use the following query to look at the filegroups and files. Since the definition for all filegroups, files, and objects are in primary, you will see the objects as included. However, and filegroups that contain files that are not online will be unavailable with a file state of RECOVERY_PENDING. Querying any tables with data that is offline will fail.

-- Attempt select from first table
Select *
From TestPiecemealRestores.dbo.SecondaryFGData;
Go

-- Attempt select from second table
Select *
From TestPiecemealRestores.dbo.TertiaryFGData;
Go

The first query above returns data, but the second query returns an error that the table resides in a filegroup that is not online.

Msg 8653, Level 16, State 1, Line 3
The query processor is unable to produce a plan for the table or view ''TertiaryFGData'' because the table resides in a filegroup which is not online.

Now let’s say that I restored the critical filegroups for someone, and they now realize that they also need data from the read-only filegroup. Your first thought may be that you have to start over because the database was not left in a recovering mode. However, if you think back to the query where we looked at the files and filegroups, the read-only filegroup is in a Recovery_Pending state. So, yes, we can restore that filegroup without starting over.

We don’t have a filegroup backup of the read-only filegroup, so I have to use the full backup I took first. I need to tell it to restore the filegroup by name, and I need to tell it where to put the file since it can’t go into the same spot.

-- Attempt to restore the Read-only filegroup from full backup
Restore Database TestPiecemealRestores
    Filegroup = 'TertiaryFG'
    From Disk = 'd:\backup\TestPiecemealRestores.bak'
    With Move 'TertiaryFile' To 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL13\MSSQL\DATA\TestPiecemealRestores_TertiaryFile.ndf';

Under normal circumstances, in order to restore the third filegroup, we would have to restore transaction log backups to bring the filegroup current with the rest of the database. However, the database has been read-only since the backup we used for the restore, and SQL is smart enough to know that there are no transactions to add to it. the moment I restored the read-only filegroup, it came online.

If we check the state of the files and filegroups now, we will see that they are all online now. and if we run the same 2 queries as before, both queries will successfully return data.

-- Check state of files in database
-- All filegroups and files should be online
-- Nothing should show as recovery pending
Select DF.name As [File Name], 
    DF.type_desc As [File Type],
    DF.state_desc As [File State],
    DF.size As [File Size],
    DS.name As [FileGroup Name]
From TestPiecemealRestores.sys.database_files DF
Left Join TestPiecemealRestores.sys.data_spaces DS On DS.data_space_id = DF.data_space_id;

-- Attempt select from first table
Select *
From TestPiecemealRestores.dbo.SecondaryFGData;
Go

-- Attempt select from second table
Select *
From TestPiecemealRestores.dbo.TertiaryFGData;

Summary

The reason I like to talk about talking about things like piecemeal restores, is that it gives us really good insight into how things work. Understanding how things work and why they work is key to figuring out how to do things that aren’t spelled out for you in a manual somewhere. It enables thinking out of the box and doing things that you might otherwise assume is undo-able.

You can download the code for this demo here: Demo_PiecemealRestoreRestore.zip (2 KB)