So far, we discussed many de-facto details about SQL Server database backup and restore. In this 15^th article of the series, we are going to discuss, the file-group and piecemeal database backup and restore process.

Database “Backup and Restore” strategies are vital to every organization for smoother functioning of the business. Database design concepts are also important in defining the backup and restore strategy. A good database design structure and proper planning would give us an ample time to speed up the recovery process.

In this article, we will discuss the following topics:

1. Introduction
2. Explain file-group(s) level database backup and restore operations
3. Discuss piecemeal database restore process
4. Demo
5. And more…

In some cases, taking full database backup sis not a big deal, whereas, for VLDB databases or large OLTP databases, it may not be a feasible solution to initiate frequent full database backups in-and-out. In such scenarios, the file(s) and filegroup(s) backup and restore options play a vital role.

If you are operating VLDB database, in some cases, it becomes a daunting task to perform full database backup and restore as it may take several hours to complete the backup and restore operation.

Piecemeal restore helps with databases that contain multiple filegroups to be restored and recovered at multiple stages. This would give an option to customize the backup and restore (or recovery) solution.

Based on recommended practices and database design principles; if the database is designed to leverage data and segments to different file groups and store them on a different drive this provides a great advantage when doing backups of the database, and restoring the database in case of any database corruption or failure. Let’s say that one of the non-primary data files may become corrupt or otherwise it can go offline due to some hardware failure then there is no need to perform the full database restores, instead, only restore the filegroup that are needed. This operation will suffice or speed-up the entire restoration process.

Getting started

Let us jump into the demo to see how to perform the backup and restore operation.

In most of the cases, a single data file and log file works best for the database design requirement. If you’re planning to leverage data across multiple data files, create secondary file groups for the data and indexes, and make the secondary filegroup a default one for the storage. In this way, the primary-file will contain only the system objects. Then it’s possible that a single file group’s data file may become corrupted or otherwise go offline due to hardware failure or I/O subsystem failure. When this happens, there’s no need to perform a full database restore. After all, the rest of the file groups are all still safe and sound. By only restoring the file groups that need it, this way you can speed up the entire restoration process. 

Let’s go ahead and complete the prep work by executing the following the T-SQL:

1. The SQLShackFGDB database is created for the demo.

   1 2 3

   CREATE DATABASE SQLShackFGDB; GO USE SQLShackFGDB;

2. Change the recovery model of the SQLShackFGDB database to FULL

   1	ALTER DATABASE SQLShackFGDB SET RECOVERY FULL;

3. Next, add additional file archiveData.ndf to the filegroup SecondarySQLShackFGDB to a SQLShackFGDB database
   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   ALTER DATABASE SQLShackFGDB ADD FILEGROUP SecondarySQLShackFGDB; GO   ALTER DATABASE SQLShackFGDB ADD FILE (     NAME = archiveData,     FILENAME = 'f:\powerSQL\archiveData.ndf',     SIZE = 5MB,     MAXSIZE = 100MB,     FILEGROWTH = 10MB ) TO FILEGROUP SecondarySQLShackFGDB;

4. Now, the database has two filegroups
   1. Primary
   2. SecondarySQLShackFGDB
   1 2 3 4 5 6 7

   SELECT DB_NAME() databasename, sf.name FileName, size/128 SizeMB, fg.name FGName,sf.physical_name FROM sys.database_files sf INNER JOIN sys.filegroups fg ON sf.data_space_id=fg.data_space_id

   
   

   

5. Verify the location and it’s status of all the respective data and log files

   1 2 3

   SELECT name, physical_name, state_desc     FROM sys.master_files     WHERE database_id = DB_ID('SQLShackFGDB');

   
   

   

6. Now, let’s use these two file groups, and create two tables. First table called ActiveSQLShackAuthor , and we’ll store it on the primary file group. Then, a second table called InactiveSQLShackAuthor, and this one on the SecondarySQLShackFGDB file group.
   1 2 3 4 5 6 7 8 9 10

   CREATE TABLE ActiveSQLShackAuthor  (     ID int IDENTITY(1,1) PRIMARY KEY,     AUthorName nvarchar(100) NOT NULL); GO   CREATE TABLE InactiveSQLShackAuthor (     ID int IDENTITY(1,1) PRIMARY KEY,     AUthorName nvarchar(100) NOT NULL) ON SecondarySQLShackFGDB; GO

7. Populate data into these tables

   1 2 3 4 5 6

   INSERT INTO ActiveSQLShackAuthor (AUthorName) values('Active1'),('Active2'),('Active3'),('Active4'),('Active5')   GO INSERT INTO InactiveSQLShackAuthor (AUthorName) values('Inactive1'),('Inactive2'),('Inactive3'),('Inactive4'),('Inactive5')

8. Verify the existence of inserted data into the SQL table

   1 2	SELECT * FROM ActiveSQLShackAuthor; SELECT * FROM InActiveSQLShackAuthor;

   
   

   

9. The following query list all the objects that are create on all the filegroups in the database

   1 2 3 4 5 6 7 8 9

   SELECT OBJECT_NAME(st.object_id) AS ObjectName, sds.name AS FileGroup     FROM sys.data_spaces sds         JOIN sys.indexes si on si.data_space_id = sds.data_space_id         JOIN sys.tables st  on st.object_id = si.object_id     WHERE si.index_id < 2         AND st.type = 'U'; GO

   
   

   

This section walkthrough the backup and restore step. 

First, initiate a backup of the entire database using full database backup command. The WITH format option is used to override the already existing backups in the f:/PowerSQL/ folder.

Let’s insert few more records into the InactiveSQLShackAuthor table.

I’ll execute the following statement to create the backup of the filegroup.

Let’s simulate the hardware failure event by deleting the files. Now, the SQL Server won’t be able to access the secondary filegroup.

1. Bring the database offline

   1 2 3

   USE MASTER; GO ALTER DATABASE SQLShackFGDB SET OFFLINE WITH ROLLBACK IMMEDIATE

   
   

   

2. Locate the the secondary file and delete

   

3. Now, try to bring the database online

   1 2 3

   USE MASTER; GO ALTER DATABASE SQLShackFGDB SET ONLINE

   
   

   

4. Check the error-log to isolate the issue. As we’ve deleted the file, the error-log report about the missing file.

   

5. Initiate a tail-log backup to recover the newly added data entries from the transaction log file.

   1 2 3 4 5 6 7

   USE MASTER GO -- need a tail log backup first BACKUP LOG SQLShackFGDB    TO DISK = 'f:\PowerSQL\SQLShackFGDBTaillog.bak'    WITH NO_TRUNCATE; GO

6. Now, restore the secondary filegroup from the backup with NORECOVERY option.

   1 2 3 4 5 6

   RESTORE DATABASE SQLShackFGDB    FILE = 'archiveData',    FILEGROUP = 'SecondarySQLShackFGDB'    FROM DISK = 'f:\PowerSQL\SecondarySQLShackFGDB.bak'    WITH NORECOVERY GO

   
   

   

7. Apply the tail log to the database to bring it online

   1 2 3 4

   RESTORE LOG SQLShackFGDB    FROM DISK = 'f:\PowerSQL\SQLShackFGDBTaillog.bak'    WITH RECOVERY GO

   
   

   

8. Let’s go ahead validate the recovery process by querying the SQL table.

   1 2	SELECT * FROM ActiveSQLShackAuthor; SELECT * FROM InActiveSQLShackAuthor;

   

Piecemeal restore

Piecemeal restore process involves a series of restore step sequences, starting with the primary and, one or more secondary read-write filegroups followed by read-only filegroups.

In some scenarios, we need to do a database restore from the backup. As we know, we do have the option to restore required file groups but not all of the file groups are requirered to make the database online at a specific instance. It is always required to restore the primary file group but any secondary user defined file groups are optional, at that point, while doing the restore. After the restore, one could get partial data and it’s available online and for the rest of the data, the users can wait, for a period of time,to recover other filegroups.

The RESTORE DATABASE command with PARTIAL clause starts a new piecemeal restore operation. The keyword PARTIAL indicates that the restore process involves a partial restore. The partial keyword defines and initiates the partial-restore sequence. This will be validated during the recovery stages. The state of the database restores remains to be recovery pending because their database recovery has been postponed.

Let us follow the below steps to prove the concept of the piecemeal process

1. First, add another filegroup to store the static data

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   ALTER DATABASE SQLShackFGDB ADD FILEGROUP ReadOnlySQLShackFGDB; GO   ALTER DATABASE SQLShackFGDB ADD FILE (     NAME = readonlyData,     FILENAME = 'f:\powerSQL\readonlydata.ndf',     SIZE = 5MB,     MAXSIZE = 100MB,     FILEGROWTH = 10MB ) TO FILEGROUP ReadOnlySQLShackFGDB; GO

2. Add a table to the filegroup ReadOnlySQLShackFGDB and insert few records to the table

   1 2 3 4 5 6 7 8 9 10 11 12

   CREATE TABLE ReportSQLShackAuthor (     ID int IDENTITY(1,1) PRIMARY KEY,     AUthorName nvarchar(100) NOT NULL) ON ReadOnlySQLShackFGDB; GO   INSERT INTO ReportSQLShackAuthor (AUthorName) values('Report1'), ('Report2'), ('Report3'), ('Report4'), ('Report5')

3. Query the table to validate the data

   1	select * from ReportSQLShackAuthor

   
   

   

4. To change the filegroup state to read_only use the following alter database command

   1 2 3

   use master GO ALTER DATABASE SQLShackFGDB MODIFY FILEGROUP ReadOnlySQLShackFGDB READ_ONLY;

   

5. Backup the SQLShackFGDB database

   1 2 3 4

   BACKUP DATABASE SQLShackFGDB     TO DISK = 'f:\PowerSQL\SQLShackFGDB.bak'     WITH FORMAT; GO

6. The database has three filegroup, One is read-only and other two are in read-write mode. Verify the filegroup status by executing the following T-SQL

   1 2 3 4 5 6 7 8 9 10

   SELECT DB_NAME() databasename, sf.name FileName, size/128 SizeMB, fg.name FGName,sf.physical_name, sf.state_desc, sf.is_read_only FROM sys.database_files sf INNER JOIN sys.filegroups fg ON sf.data_space_id=fg.data_space_id

   

7. Backup the read-only database

   1 2 3 4 5

   BACKUP DATABASE SQLShackFGDB    FILEGROUP = 'ReadOnlySQLShackFGDB'    TO DISK = 'f:\PowerSQL\ReadOnlySQLShackFGDB.bak' WITH FORMAT GO

   
   

   

8. Now, drop the database to simulate the piecemeal recovery process of the database

   1 2 3

   USE MASTER; GO DROP DATABASE SQLShackFGDB

9. Let’s perform the database restore operation. Before you start, change the session context to master database. Now, we’re going to do the read-write filegroups restore using READ_WRITE_FILEGROUPS clause.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   USE MASTER GO RESTORE DATABASE SQLShackFGDB READ_WRITE_FILEGROUPS FROM DISK = 'f:\PowerSQL\SQLShackFGDB.bak'   WITH PARTIAL, RECOVERY   GO SELECT TOP (1000) [ID]       ,[AUthorName]   FROM [SQLShackFGDB].[dbo].[InactiveSQLShackAuthor]     GO     SELECT TOP (1000) [ID]       ,[AUthorName]              FROM [SQLShackFGDB].[dbo].ReportSQLShackAuthor

   
   

   

10. Next, restore the read-only filegroups

    1 2 3 4 5

    RESTORE DATABASE SQLShackFGDB    FILE = 'readonlyData',    FILEGROUP = 'ReadOnlySQLShackFGDB'    FROM DISK = 'f:\PowerSQL\ReadOnlySQLShackFGDB.bak'    WITH RECOVERY

    
    

    

11. Verify the output by querying the read-only table data

    1 2 3 4 5 6 7 8 9

    SELECT TOP (1000) [ID]       ,[AUthorName]   FROM [SQLShackFGDB].[dbo].[InactiveSQLShackAuthor]     GO     SELECT TOP (1000) [ID]       ,[AUthorName]              FROM [SQLShackFGDB].[dbo].ReportSQLShackAuthor

    
    

    

Wrapping up

This article walkthrough the database backup and restore (or recovery) of a SQL Server that contain multiple files or filegroups.

We also talked about file and filegroup level backup and the available options to restore partial databases with the concept of a piecemeal restore. We saw how to perform the database recovery process by enabling filegroup backups. There is an option available, that we reviewed, to speed up the recovery process without having to restore the entire database.

Table of contents

References

• BACKUP (Transact-SQL)
• Example: Piecemeal Restore of Only Some Filegroups (Full Recovery Model)
• Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021