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:
- Explain file-group(s) level database backup and restore operations
- Discuss piecemeal database restore process
- 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.
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:
The SQLShackFGDB database is created for the demo.123CREATE DATABASE SQLShackFGDB;GOUSE SQLShackFGDB;
Change the recovery model of the SQLShackFGDB database to FULL1ALTER DATABASE SQLShackFGDB SET RECOVERY FULL;
Next, add additional file archiveData.ndf to the filegroup SecondarySQLShackFGDB to a SQLShackFGDB database
1234567891011121314ALTER DATABASE SQLShackFGDBADD FILEGROUP SecondarySQLShackFGDB;GOALTER DATABASE SQLShackFGDBADD FILE(NAME = archiveData,FILENAME = 'f:\powerSQL\archiveData.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)TO FILEGROUP SecondarySQLShackFGDB;
Now, the database has two filegroups
1234567SELECT DB_NAME() databasename,sf.name FileName,size/128 SizeMB,fg.name FGName,sf.physical_nameFROM sys.database_files sfINNER JOINsys.filegroups fg ON sf.data_space_id=fg.data_space_id
Verify the location and it’s status of all the respective data and log files123SELECT name, physical_name, state_descFROM sys.master_filesWHERE database_id = DB_ID('SQLShackFGDB');
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.
12345678910CREATE TABLE ActiveSQLShackAuthor (ID int IDENTITY(1,1) PRIMARY KEY,AUthorName nvarchar(100) NOT NULL);GOCREATE TABLE InactiveSQLShackAuthor (ID int IDENTITY(1,1) PRIMARY KEY,AUthorName nvarchar(100) NOT NULL)ON SecondarySQLShackFGDB;GO
Populate data into these tables123456INSERT INTO ActiveSQLShackAuthor (AUthorName)values('Active1'),('Active2'),('Active3'),('Active4'),('Active5')GOINSERT INTO InactiveSQLShackAuthor (AUthorName)values('Inactive1'),('Inactive2'),('Inactive3'),('Inactive4'),('Inactive5')
Verify the existence of inserted data into the SQL table12SELECT * FROM ActiveSQLShackAuthor;SELECT * FROM InActiveSQLShackAuthor;
The following query list all the objects that are create on all the filegroups in the database123456789SELECTOBJECT_NAME(st.object_id) AS ObjectName,sds.name AS FileGroupFROM sys.data_spaces sdsJOIN sys.indexes si on si.data_space_id = sds.data_space_idJOIN sys.tables st on st.object_id = si.object_idWHERE si.index_id < 2AND 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.
-- backup the database
BACKUP DATABASE SQLShackFGDB
TO DISK = 'f:\PowerSQL\SQLShackFGDB.bak'
Let’s insert few more records into the InactiveSQLShackAuthor table.
INSERT INTO InactiveSQLShackAuthor (AuthorName)
I’ll execute the following statement to create the backup of the filegroup.
-- backup the secondary filegroup by itself
BACKUP DATABASE SQLShackFGDB
FILEGROUP = 'SecondarySQLShackFGDB'
TO DISK = 'f:\PowerSQL\SecondarySQLShackFGDB.bak'
Let’s simulate the hardware failure event by deleting the files. Now, the SQL Server won’t be able to access the secondary filegroup.
Bring the database offline123USE MASTER;GOALTER DATABASE SQLShackFGDB SET OFFLINE WITH ROLLBACK IMMEDIATE
Locate the the secondary file and delete
Now, try to bring the database online123USE MASTER;GOALTER DATABASE SQLShackFGDB SET ONLINE
Check the error-log to isolate the issue. As we’ve deleted the file, the error-log report about the missing file.
Initiate a tail-log backup to recover the newly added data entries from the transaction log file.1234567USE MASTERGO-- need a tail log backup firstBACKUP LOG SQLShackFGDBTO DISK = 'f:\PowerSQL\SQLShackFGDBTaillog.bak'WITH NO_TRUNCATE;GO
Now, restore the secondary filegroup from the backup with NORECOVERY option.123456RESTORE DATABASE SQLShackFGDBFILE = 'archiveData',FILEGROUP = 'SecondarySQLShackFGDB'FROM DISK = 'f:\PowerSQL\SecondarySQLShackFGDB.bak'WITH NORECOVERYGO
Apply the tail log to the database to bring it online1234RESTORE LOG SQLShackFGDBFROM DISK = 'f:\PowerSQL\SQLShackFGDBTaillog.bak'WITH RECOVERYGO
Let’s go ahead validate the recovery process by querying the SQL table.12SELECT * FROM ActiveSQLShackAuthor;SELECT * FROM InActiveSQLShackAuthor;
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
First, add another filegroup to store the static data123456789101112131415ALTER DATABASE SQLShackFGDBADD FILEGROUP ReadOnlySQLShackFGDB;GOALTER DATABASE SQLShackFGDBADD FILE(NAME = readonlyData,FILENAME = 'f:\powerSQL\readonlydata.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)TO FILEGROUP ReadOnlySQLShackFGDB;GO
Add a table to the filegroup ReadOnlySQLShackFGDB and insert few records to the table123456789101112CREATE TABLE ReportSQLShackAuthor (ID int IDENTITY(1,1) PRIMARY KEY,AUthorName nvarchar(100) NOT NULL)ON ReadOnlySQLShackFGDB;GOINSERT INTO ReportSQLShackAuthor (AUthorName)values('Report1'),('Report2'),('Report3'),('Report4'),('Report5')
Query the table to validate the data1select * from ReportSQLShackAuthor
To change the filegroup state to read_only use the following alter database command123use masterGOALTER DATABASE SQLShackFGDB MODIFY FILEGROUP ReadOnlySQLShackFGDB READ_ONLY;
Backup the SQLShackFGDB database1234BACKUP DATABASE SQLShackFGDBTO DISK = 'f:\PowerSQL\SQLShackFGDB.bak'WITH FORMAT;GO
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-SQL12345678910SELECT DB_NAME() databasename,sf.name FileName,size/128 SizeMB,fg.name FGName,sf.physical_name,sf.state_desc,sf.is_read_onlyFROM sys.database_files sfINNER JOINsys.filegroups fgON sf.data_space_id=fg.data_space_id
Backup the read-only database12345BACKUP DATABASE SQLShackFGDBFILEGROUP = 'ReadOnlySQLShackFGDB'TO DISK = 'f:\PowerSQL\ReadOnlySQLShackFGDB.bak'WITH FORMATGO
Now, drop the database to simulate the piecemeal recovery process of the database123USE MASTER;GODROP DATABASE SQLShackFGDB
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.123456789101112131415USE MASTERGORESTORE DATABASE SQLShackFGDB READ_WRITE_FILEGROUPSFROM DISK = 'f:\PowerSQL\SQLShackFGDB.bak'WITH PARTIAL, RECOVERYGOSELECT TOP (1000) [ID],[AUthorName]FROM [SQLShackFGDB].[dbo].[InactiveSQLShackAuthor]GOSELECT TOP (1000) [ID],[AUthorName]FROM [SQLShackFGDB].[dbo].ReportSQLShackAuthor
Next, restore the read-only filegroups12345RESTORE DATABASE SQLShackFGDBFILE = 'readonlyData',FILEGROUP = 'ReadOnlySQLShackFGDB'FROM DISK = 'f:\PowerSQL\ReadOnlySQLShackFGDB.bak'WITH RECOVERY
Verify the output by querying the read-only table data123456789SELECT TOP (1000) [ID],[AUthorName]FROM [SQLShackFGDB].[dbo].[InactiveSQLShackAuthor]GOSELECT TOP (1000) [ID],[AUthorName]FROM [SQLShackFGDB].[dbo].ReportSQLShackAuthor
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
- BACKUP (Transact-SQL)
- Example: Piecemeal Restore of Only Some Filegroups (Full Recovery Model)
- Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021
- How to perform Azure SQL database Import/Export operations using PowerShell - January 14, 2021
- Different ways to login to Azure automation using PowerShell - December 22, 2020