Basics Of Database Administration In SQL Server: Part 7

Introduction

In my previous article Basics of Database Administration in SQL Server: Part 6, I discussed 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.

 

If you missed previous articles you can check them out from the following link:

• Basics of Database Administration in SQL Server: Part 1
• Basics of Database Administration in SQL Server: Part 2
• Basics of Database Administration in SQL Server: Part 3
• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5
• Basics of Database Administration in SQL Server: Part 6

What is Piecemeal Restore

Piecemeal restore is a more advanced 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, started with the primary filegroup. Piecemeal restore was introduced in SQL Server 2005.

Piecemeal restore works with only Enterprise Edition of SQL Server 2005 or later versions contain multiple filegroups.

 

Piecemeal Restore Exampled Step by Step:

To understand the concept of piecemeal restore, let’s take it with 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 the following script to create our desired database for testing piecemeal restore.

1. CREATE DATABASE[TestDB] ON  
2. PRIMARY(NAME = N 'PrimaryTest', FILENAME = N 'D:\TestDB\TestDB.mdf'),  
3.   
4.     FILEGROUP[FG1](NAME = N 'TestDB_FG1', FILENAME = N 'D:\TestDB\TestDB_FG1.ndf'),  
5.   
6.     FILEGROUP[FG2](NAME = N 'TestDB_FG2', FILENAME = N 'D:\TestDB\TestDB_FG2.ndf'),  
7.   
8.     FILEGROUP[FG3](NAME = N 'TestDB_FG3', FILENAME = N 'D:\TestDB\TestDB_FG3.ndf')  
9.   
10. LOG ON(NAME = N 'TestDB_log', FILENAME = N 'D:\TestDB\TestDB_log.ldf')  
11. GO  

Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model. In our example we have chosen FULL RECOVERY MODEL so that we are able to perform log backups as well.

 

                                       Figure 1: TestDB created with 4 filgroups

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 the following script:

1. USE testdb  
2. Go  
3.   
4. CREATE TABLE T1  
5.     (ID INT)  
6. ON FG1  
7. Go  
8. INSERT INTO T1 VALUES(1)  
9. Go  
10.   
11.   
12. CREATE TABLE T2  
13.     (ID INT)  
14. ON FG2  
15. Go  
16. INSERT INTO T2 VALUES(1)  
17. Go  
18.   
19.   
20. CREATE TABLE T3  
21.     (ID INT)  
22. ON FG3  
23. Go  
24. INSERT INTO T3 VALUES(1)  
25. Go  
26.   
27.   
28. CREATE TABLE T4  
29.     (ID INT)  
30. ON[PRIMARY]  
31. Go  
32. INSERT INTO T4 VALUES(1)  
33. Go  

Step 3:

In step 3 we will take backup of all filegroups separately, apart from that you can also take full backup of the database to be on safer side and it’s always a best practice to take full backup of databases. The following are the scripts to take full backup of the database and filegroups backup.

 

Remember: We have taken 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.

1. --BACKUP DATABASE[TestDB] TO  
2.     --DISK = N 'D:\TestDB Backups\TestDBFullBackUp.bak'  
3. WITH NOFORMAT, NOINIT,  
4. --NAME = N 'TestDB-Full-Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
5.     --GO  
6.   
7.   
8. --Backup of Primary filegroup  
9. BACKUP DATABASE[TestDB] FILEGROUP = N 'PRIMARY'  
10. TO DISK = N 'D:\TestDB Backups\PrimaryFG.bak'  
11. WITH NOFORMAT, NOINIT,  
12. NAME = N 'PrimaryFG Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
13. GO  
14.   
15.   
16. --Backup of FG1 filegroup  
17. BACKUP DATABASE[TestDB] FILEGROUP = N 'FG1'  
18. TO DISK = N 'D:\TestDB Backups\fg1.bak'  
19. WITH NOFORMAT, NOINIT,  
20. NAME = N 'FG1 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
21. GO  
22.   
23.   
24. --Backup of FG2 filegroup  
25. BACKUP DATABASE[TestDB] FILEGROUP = N 'FG2'  
26. TO DISK = N 'D:\TestDB Backups\fg2.bak'  
27. WITH NOFORMAT, NOINIT,  
28. NAME = N 'FG2 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
29. GO  
30.   
31.   
32. --Backup of FG3 filegroup  
33. BACKUP DATABASE[TestDB] FILEGROUP = N 'FG3'  
34. TO DISK = N 'D:\TestDB Backups\fg3.bak'  
35. WITH NOFORMAT, NOINIT,  
36. NAME = N 'FG3 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
37. GO  

Step 4:

In step 4, further we will insert values in tables T1, T2, T3 and T4.

1. INSERT INTO T1 VALUES(2)  
2. INSERT INTO T2 VALUES(2)  
3. INSERT INTO T3 VALUES(2)  
4. INSERT INTO T4 VALUES(2)  

Step 5:

In step 5, we are going to take log backup of TestDB with below script:

1. BACKUP LOG [TestDB] TO  
2. DISK = N'D:\TestDB Backups\translog1.trn' WITH NOFORMAT, NOINIT,  
3. NAME = N'TransactionLog1 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
4. GO  

Step 6:

In step 6, we again inserted values in all tables i.e. T1, T2, T3 and T4 and taken log backup as well.

1. INSERT INTO T1 VALUES(3)  
2. INSERT INTO T2 VALUES(3)  
3. INSERT INTO T3 VALUES(3)  
4. INSERT INTO T4 VALUES(3)  
5. Go  
6.   
7. BACKUP LOG [TestDB] TO  
8. DISK = N'D:\TestDB Backups\translog2.trn' WITH NOFORMAT, NOINIT,  
9. NAME = N'TransactionLog2 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10  
10. GO  

Step 7:

In step7, we again inserted some values in table T1, T2, T3 and T4 and performed log backup.

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

Step 8:

In step 8, we again inserted some values in table T1, T2, T3 and T4.

1. INSERT INTO T1 VALUES(5)  
2. INSERT INTO T2 VALUES(5)  
3. INSERT INTO T3 VALUES(5)  
4. INSERT INTO T4 VALUES(5)  
5. Go  

Step 9:

In step 9, we are performing tail log backup.

1. Use master  
2. go  
3. BACKUP LOG [TestDB] TO DISK = N'D:\TestDB Backups\translogtail.trn'  
4. WITH NO_TRUNCATE , NOFORMAT, NOINIT,  
5. NAME = N'TLogTail Backup',  
6. SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10  
7. Go  

We need to use master database while performing tail log backup and restore operations, otherwise it will through error: Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database 'TestDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally. [Error for tail log backup]


Msg 3102, Level 16, State 1, Line 1
RESTORE cannot process database 'TestDB' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. [Error for restore operations]

 

                  Figure 2: tail log backup can only operate using master database

                     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 the following sequence. First we will apply primaryfg.bak and then all transaction log backups i.e. translog1.trn, translog2.trn, translog.3 and translogtail.trn:

1. RESTORE DATABASE TestDB FILEGROUP = 'Primary'  
2. FROM DISK = N 'D:\TestDB Backups\primaryfg.bak'  
3. WITH PARTIAL, NORECOVERY  
4. Go  
5.   
6. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog1.trn'  
7. WITH NORECOVERY  
8. Go  
9.   
10.   
11. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog2.trn'  
12. WITH NORECOVERY  
13. Go  
14.   
15.   
16. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog3.trn'  
17. WITH NORECOVERY  
18. Go  
19.   
20.   
21. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translogtail.trn'  
22. WITH RECOVERY  
23. Go  

Step 11: Now we will check the status of our TestDB database with the following query:

1. SELECT [name], [state_desc]  
2. FROM TestDB.sys.database_files;  
3. GO  

      Figure 4: State of TestDB database after restoring PRIMARY filegroup

From the above output we can see that PRIMARY filegroup is now ONLINE while other filegroups i.e. FG1, FG2 and FG3 are still in RECOVERY_PENDING state. This is the actual beauty of doing piecemeal restore. It is very helpful when we have a very large database. In this situation we can restore PRIMARY filegroup first then other filegroup in stages and business or users will not get hampered. Here we can remember one thing that restore that filegroup first in which important tables are there and users use frequently, 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 tables because these tables resides in FG1, FG2 and FG3 filegroup which are in RECOVERY_PENDING state.

Figure 5: able to fetch data from T4 table which resides in PRIMARY filegroup

We got the following 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 step12, let’s apply restore operations on other filegroups as well to make TestDB database online.

For this you need to run the following queries in sequential order:

1. use master  
2. go  
3. RESTORE DATABASE TestDB FILEGROUP = 'fg1'  
4. FROM DISK = N 'D:\TestDB Backups\fg1.bak'  
5. WITH NORECOVERY  
6. Go  
7.   
8. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog1.trn'  
9. WITH NORECOVERY  
10. Go  
11.   
12.   
13. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog2.trn'  
14. WITH NORECOVERY  
15. Go  
16.   
17.   
18. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog3.trn'  
19. WITH NORECOVERY  
20. Go  
21.   
22.   
23. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translogtail.trn'  
24. WITH RECOVERY  
25. Go  

Now check the state of TestDB database again using the following query:

1. SELECT [name], [state_desc]  
2. FROM TestDB.sys.database_files;  
3. GO  

            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 step13, do restore operation for FG2 filegroup with the following query:

1. use master  
2. RESTORE DATABASE TestDB FILEGROUP = 'fg2'  
3. FROM DISK = N 'D:\TestDB Backups\fg2.bak'  
4. WITH NORECOVERY  
5. Go  
6.   
7. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog1.trn'  
8. WITH NORECOVERY  
9. Go  
10.   
11.   
12. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog2.trn'  
13. WITH NORECOVERY  
14. Go  
15.   
16.   
17. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog3.trn'  
18. WITH NORECOVERY  
19. Go  
20.   
21.   
22. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translogtail.trn'  
23. WITH RECOVERY  
24. Go  

Step 14: In step14, repeat the restore operation for FG3 filegroup using the following query:

1. use master  
2. go  
3. RESTORE DATABASE TestDB FILEGROUP = 'fg3'  
4. FROM DISK = N 'D:\TestDB Backups\fg3.bak'  
5. WITH NORECOVERY  
6. Go  
7.   
8. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog1.trn'  
9. WITH NORECOVERY  
10. Go  
11.   
12.   
13. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog2.trn'  
14. WITH NORECOVERY  
15. Go  
16.   
17.   
18. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translog3.trn'  
19. WITH NORECOVERY  
20. Go  
21.   
22.   
23. RESTORE LOG TestDB FROM DISK = N 'D:\TestDB Backups\translogtail.trn'  
24. WITH RECOVERY  
25. Go  

Step 15: In step15, now check the final state of TestDB database with the following query:

1. SELECT [name], [state_desc]  
2. FROM TestDB.sys.database_files;  
3. GO  

   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.

Final Words

Now after the explained example it is very clear how we can make our database ONLINE using multiple filegroups. This is actually the piecemeal restore which helps us a lot to restore large databases and makes the important part of database ONLINE first after making PRIMARY FILEGROUP online. You can contribute with your comments if any correction is needed.

 

Note: Note: All the screenshots are applied to SQL Server 2012 Enterprise Evaluation Edition.

 

References

1. Piecemeal Restores
2. Piecemeal Restore SQL Server Customer Advisory Team
3. SQLSoldier
4. LessThanDot