31 Days of Disaster Recovery

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 FilesMicrosoft SQL ServerMSSQL11.SQL12MSSQLDATATestPiecemealRestores_Secondary.ndf')

TO FileGroup SecondaryFG;

GO
-- Add file for second table

Alter Database TestPiecemealRestores

Add File (

Name = N'TertiaryFile',

FileName = N'C:\Program FilesMicrosoft SQL ServerMSSQL11.SQL12MSSQLDATATestPiecemealRestores_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:\backupTestPiecemealRestores.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 ReadWrite filegroups (primary and SecondaryFG)

Backup Database TestPiecemealRestores

Read_Write_Filegroups

To Disk = 'd:\backupTestPiecemealRestores_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:\backupTestPiecemealRestores_RW.bak'

With Move 'TestPiecemealRestores' To 'C:\Program FilesMicrosoft SQL ServerMSSQL11.SQL13MSSQLDATATestPiecemealRestores.mdf',

Move 'TestPiecemealRestores_log' To 'C:\Program FilesMicrosoft SQL ServerMSSQL11.SQL13MSSQLDATATestPiecemealRestores_log.ldf',

Move 'SecondaryFile' To 'C:\Program FilesMicrosoft SQL ServerMSSQL11.SQL13MSSQLDATATestPiecemealRestores_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:\backupTestPiecemealRestores.bak'

With Move 'TertiaryFile' To 'C:\Program FilesMicrosoft SQL ServerMSSQL11.SQL13MSSQLDATATestPiecemealRestores_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;