SQL Server Piecemeal Restore Explained

Yashwant VishwakarmaSQL DBA
CERTIFIED EXPERT
Published:
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Piecemeal Restore/Partial Restore:
 
Note: All screenshots are applied to SQL Server 2012 Enterprise Evaluation Edition.
 
1. Introduction:
In my previous article SQL Server Storage Basics: Database Filegroups, I discussed about database filegroups, why filegroups are important and how it helps in database administration. There I mentioned that we can bring some parts of the database online quickly (piecemeal restore/partial). This article is all about Piecemeal Restore / Partial Restore, so without wasting time let’s get started with piecemeal restore. 
2. What is Piecemeal Restore?
Piecemeal restore is a more advance form of partial restore in SQL Server 2000. Actually piecemeal restore is composed of sequential restores and recovers a database in stages at the filegroup level, starting with the primary filegroup. Piecemeal restore was introduced in SQL Server 2005.
 note1.png
 
3. Piecemeal Restore Exampled Step by Step:
To understand the concept of piecemeal restore, let’s look at an example step by step:
 
Step 1:
We are going to create a database named TestDB with FOUR filegroups named Primary, FG1, FG2 and FG3 in which Primary filegroup is the default filegroup. We can use this script to create our desired database. 
CREATE DATABASE [TestDB] ON 
                      PRIMARY ( NAME = N'PrimaryTest', FILENAME = N'D:\TestDB\TestDB.mdf' ),
                       
                      FILEGROUP [FG1] ( NAME = N'TestDB_FG1', FILENAME = N'D:\TestDB\TestDB_FG1.ndf' ),
                       
                      FILEGROUP [FG2] ( NAME = N'TestDB_FG2', FILENAME = N'D:\TestDB\TestDB_FG2.ndf' ),
                       
                      FILEGROUP [FG3] ( NAME = N'TestDB_FG3', FILENAME = N'D:\TestDB\TestDB_FG3.ndf' )
                       
                      LOG ON ( NAME = N'TestDB_log', FILENAME = N'D:\TestDB\TestDB_log.ldf')
                      GO

Open in new window

note2.png

Step 2:
After creating TestDB with FOUR filegroups, we are going to create and insert values in table T1 on filegroup FG1, table T2 on FG2, table T3 on FG3 and table T4 on Primary filegroup with below script:
 

USE testdb
                      Go
                       
                      CREATE TABLE T1
                      (ID INT)
                      ON FG1
                      Go
                      INSERT INTO T1 VALUES(1)
                      Go
                       
                      CREATE TABLE T2
                      (ID INT)
                      ON FG2
                      Go
                      INSERT INTO T2 VALUES(1)
                      Go
                       
                      CREATE TABLE T3
                      (ID INT)
                      ON FG3
                      Go
                      INSERT INTO T3 VALUES(1)
                      Go
                       
                      CREATE TABLE T4
                      (ID INT)
                      ON [PRIMARY]
                      Go
                      INSERT INTO T4 VALUES(1)
                      Go

Open in new window


Step 3:

In Step 3 we will back up all of the filegroups separately. You can also make a full backup of the database to be on the safer side; it’s always a best practice to make a full backup. Remember: We have made a full backup of the database only for emergency cases so that we can revert back the changes if required, but we will not use it during the piecemeal restore.  

--BACKUP DATABASE [TestDB] TO 
                      --DISK = N'D:\TestDB Backups\TestDBFullBackUp.bak' WITH NOFORMAT, NOINIT, 
                      --NAME = N'TestDB-Full-Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                      --GO
                       

Open in new window

 

 --Backup of Primary filegroup
                      BACKUP DATABASE [TestDB] FILEGROUP = N'PRIMARY'
                      TO  DISK = N'D:\TestDB Backups\PrimaryFG.bak' WITH NOFORMAT, NOINIT, 
                      NAME = N'PrimaryFG Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                      GO
                       
                       
                      --Backup of FG1 filegroup
                      BACKUP DATABASE [TestDB] FILEGROUP = N'FG1'
                      TO  DISK = N'D:\TestDB Backups\fg1.bak' WITH NOFORMAT, NOINIT, 
                      NAME = N'FG1 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                      GO
                       
                       
                      --Backup of FG2 filegroup
                      BACKUP DATABASE [TestDB] FILEGROUP = N'FG2'
                      TO  DISK = N'D:\TestDB Backups\fg2.bak' WITH NOFORMAT, NOINIT, 
                      NAME = N'FG2 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                      GO
                       
                       
                      --Backup of FG3 filegroup
                      BACKUP DATABASE [TestDB] FILEGROUP = N'FG3'
                      TO  DISK = N'D:\TestDB Backups\fg3.bak' WITH NOFORMAT, NOINIT, 
                      NAME = N'FG3 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                      GO

Open in new window


Step 4:
In Step 4, we will insert more values in tables T1, T2, T3 and T4.
 
INSERT INTO T1 VALUES(2)
                      INSERT INTO T2 VALUES(2)
                      INSERT INTO T3 VALUES(2)
                      INSERT INTO T4 VALUES(2)
                       

Open in new window

 
Step 5:
In Step 5, we make a log backup of TestDB:
 
BACKUP LOG [TestDB] TO 
                      DISK = N'D:\TestDB Backups\translog1.trn' WITH NOFORMAT, NOINIT, 
                      NAME = N'TransactionLog1 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                      GO

Open in new window


Step 6:
In Step 6, we again insert values in all tables i.e. T1, T2, T3 and T4 and make another log backup as well.
 
INSERT INTO T1 VALUES(3)
                      INSERT INTO T2 VALUES(3)
                      INSERT INTO T3 VALUES(3)
                      INSERT INTO T4 VALUES(3)
                      Go
                       
                      BACKUP LOG [TestDB] TO 
                      DISK = N'D:\TestDB Backups\translog2.trn' WITH NOFORMAT, NOINIT, 
                      NAME = N'TransactionLog2 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                      GO

Open in new window

 
Step 7:

In Step 7, we again insert some values in table T1, T2, T3 and T4 and perform a log backup.
 

INSERT INTO T1 VALUES(4)
                      INSERT INTO T2 VALUES(4)
                      INSERT INTO T3 VALUES(4)
                      INSERT INTO T4 VALUES(4)
                      Go
                       
                      BACKUP LOG [TestDB] TO 
                      DISK = N'D:\TestDB Backups\translog3.trn' WITH NOFORMAT, NOINIT, 
                      NAME = N'TransactionLog3 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
                      GO

Open in new window


Step 8:
In Step 8, we again insert some values in table T1, T2, T3 and T4.
INSERT INTO T1 VALUES(5)
                      INSERT INTO T2 VALUES(5)
                      INSERT INTO T3 VALUES(5)
                      INSERT INTO T4 VALUES(5)
                      Go

Open in new window

 
Step 9:
In Step 9, we perform a tail log backup.
 
Use master
                      go
                      BACKUP LOG [TestDB] TO DISK = N'D:\TestDB Backups\translogtail.trn'
                      WITH NO_TRUNCATE , NOFORMAT, NOINIT,
                      NAME = N'TLogTail Backup',
                      SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10
                      Go

Open in new window


note3.png

tail-log-backup-error-msg.png  Figure 2: tail log backup can only operate using master database


tail-log-backup-success.PNG    Figure 3: Tail log backup successful when used master database

Step 10:

In Step 10, as we have all backups now we will perform restore operation for PRIMARY filegroup in below sequence. First we will apply primaryfg.bak and then all transaction log backups i.e. translog1.trn, translog2.trn, translog.3 and translogtail.trn:
 

USE MASTER
                      Go
                      
                      RESTORE DATABASE TestDB FILEGROUP='Primary' FROM DISK = N'D:\TestDB Backups\primaryfg.bak'
                      WITH PARTIAL, NORECOVERY
                      Go
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog1.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog2.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog3.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translogtail.trn' WITH RECOVERY
                      Go

Open in new window

 
Step 11:
Now we check the status of our TestDB database with this query:
 
SELECT [name], [state_desc]
                      FROM TestDB.sys.database_files;
                      GO
                       

Open in new window


2.png        Figure 4: State of TestDB database after restoring PRIMARY filegroup
 
From the above output we can see that PRIMARY filegroup is now ONLINE while the other filegroups i.e. FG1, FG2 and FG3 are still in RECOVERY_PENDING state. This is the actual beauty of doing a piecemeal restore. It is very helpful when we have very large database. In this situation we can restore the PRIMARY filegroup first then the other filegroups in stages and the business or users will not get hampered. Then we can restore that filegroup in which the most important tables are, after primary filegroup restore.
 

At this stage now we can select data from T4 table which is created in PRIMARY filegroup but we will not be able to select data from T1, T2 and T4 table because these tables resides in FG1, FG2 and FG3 filegroup which are in RECOVERY_PENDING state.


t4.png
Figure 5: able to fetch data from T4 table which resides in PRIMARY filegroup
 
We got below error message when we tried to fetch data from T3 table which resides in FG3 filegroup and same error message will also show when we try to fetch data from T1 and T2 table because FG1 and FG2 are in RECOVERY_PENDING state. 


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

In Step 12, let’s apply restore operations on other filegroups as well to make TestDB database online. For this you need to run below queries in sequential order:
 

use master
                      go
                      
                      RESTORE DATABASE TestDB FILEGROUP='fg1' FROM DISK = N'D:\TestDB Backups\fg1.bak'
                      WITH NORECOVERY
                      Go
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog1.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog2.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog3.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translogtail.trn' WITH RECOVERY
                      Go

Open in new window


 Now check the state of TestDB database again using below query:
 
SELECT [name], [state_desc]
                      FROM TestDB.sys.database_files;
                      GO

Open in new window


3.png        Figure 6: State of TestDB database after restoring FG1 filegroup
 
 
Now FG1 filegroup is also ONLINE, only FG2 and FG3 are in RECOVERY_PENDING state.
 
Step 13:
In Step 13, do the restore operation for FG2 filegroup with below query:
 
use master
                      go
                      
                      RESTORE DATABASE TestDB FILEGROUP='fg2' FROM DISK = N'D:\TestDB Backups\fg2.bak'
                      WITH NORECOVERY
                      Go
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog1.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog2.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog3.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translogtail.trn' WITH RECOVERY
                      Go

Open in new window


Step 14:
In Step 14, repeat the restore operation for FG3 filegroup using below query:
use master
                      go
                      
                      RESTORE DATABASE TestDB FILEGROUP='fg3' FROM DISK = N'D:\TestDB Backups\fg3.bak'
                      WITH NORECOVERY
                      Go
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog1.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog2.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translog3.trn' WITH NORECOVERY
                      Go
                       
                       
                      RESTORE LOG TestDB FROM DISK = N'D:\TestDB Backups\translogtail.trn' WITH RECOVERY
                      Go

Open in new window


Step 15:
In Step 15, check the final state of TestDB database:
 
SELECT [name], [state_desc]
                      FROM TestDB.sys.database_files;
                      GO

Open in new window


 4.png         Figure 7: State of TestDB database after restoring FG2 and FG3 filegroup


Wow It’s cool now all filegroups are ONLINE now and we can fetch data from all tables also which belongs with different filegroups.
4. Conclusion:

It is very clear that how we can make our database ONLINE using multiple filegroups. This is actually the piecemeal restore that helps us restore very large databases and make the important part of database ONLINE first after making PRIMARY FILEGROUP online. You can contribute with your comments if any correction needed. 
5. References:
1) Piecemeal Restores
2) Piecemeal Restore SQL Server Customer Advisory Team
3) http://www.sqlsoldier.com/
4) http://blogs.lessthandot.com/
0
13,134 Views
Yashwant VishwakarmaSQL DBA
CERTIFIED EXPERT

Comments (1)

Really a very usefull information thanks for step by step instructions and details...

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.