If you missed any of the earlier posts in my DR series, you can check them out here:
- 31 Days of disaster Recovery
- Does DBCC Automatically Use Existing Snapshot?
- Protection From Restoring a Backup of a Contained Database
- Determining Files to Restore Database
- Back That Thang Up
- Dealing With Corruption in a Nonclustered Index
- Dealing With Corruption in Allocation Pages
- Writing SLAs for Disaster Recover
- Resolutions for All DBAs
- Use All the Checksums
- Monitoring for Corruption Errors
- Converting LSN Formats
- Extreme Disaster Recovery Training
- Standard Backup Scripts
- Fixing a Corrupt Tempdb
- Running DBCC CheckTable in Parallel Jobs
- Disaster Recovery Gems From Around The Net
- When are Checksums Written to a Page
- How to CHECKDB Like a Boss
- How Much Log Can a Backup Log
- The Case of the Backups That Wouldn’t Restore
- Who Deleted That Data?
- Which DBCC CHECK Commands Update Last Known Good DBCC
- Restoring Differential Backups With New Files
- Handling Corruption in a Clustered Index
- Improving Performance of Backups and Restores
- The Mysterious Case of the Long Backup
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;
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)