Prashanth Jayaram

Database Filegroup(s) and Piecemeal restores in SQL Server

June 22, 2018 by

So far, we discussed many de-facto details about SQL Server database backup and restore. In this 15th 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.

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

  3. Next, add additional file archiveData.ndf to the filegroup SecondarySQLShackFGDB to a SQLShackFGDB database
  4. Now, the database has two filegroups
    1. Primary
    2. SecondarySQLShackFGDB

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


  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.
  7. Populate data into these tables

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


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


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


  2. Locate the the secondary file and delete

  3. Now, try to bring the database 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.

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


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


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

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

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

  3. Query the table to validate the data


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

  5. Backup the SQLShackFGDB database

  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

  7. Backup the read-only database


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

  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.


  10. Next, restore the read-only filegroups


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


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

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using SQL Operations Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV

References


See more

To manage SQL Server backups, consider ApexSQL Backup, a tool that offers automation of backup, restore, and log shipping jobs, stores details of all backup activities and enables easy cross server backup management and maintenance.


Prashanth Jayaram

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
Prashanth Jayaram
Backup and restore

About 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

937 Views