SQL Server: How To Perform Piecemeal Restore and Partial Backup of a Database
Introduction
We all know the three main backup types — Full, Differential and Log. A very simple and straightforward point-in-time recovery plan would involve restoring the most recent Full backup followed by the most recent Differential and the subsequent Log backups in sequence up to and including the tail-log backup. Contingent upon the recovery scenario, restoring the tail-log potentially involves the “STOPAT” command with the timestamp to indicate the specific point-in-time the database needs to be recovered to. This timestamp could be the time just before someone deleted a table or the database crashed to the point it stopped functioning and hence the need to perform point-in-time recovery.
While that is perfect in situations where we need to perform a full recovery, there are times when only one (or a few) data files in a non-primary filegroup go bad and need to be recovered without touching the rest of the filegroups. There are also times when we have data split across several filegroups and we have different RTO (Recovery Time Objective) for each filegroup such that in case of a disaster, the most critical filegroup needs to be recovered as soon as possible while the rest can be done a little later. Fortunately, SQL Server gives us the ability to restore specific data files should they crash or become corrupted. It also lets a database to be restored on a filegroup-by-filegroup basis such that the database is online following a disaster with the critical filegroup online shortly after the disaster, while the other filegroups are still being restored. This article talks about how a specific data file can be recovered without messing up the rest and how one can perform a database recovery on a filegroup-by-filegroup basis.
Note that the partial database recovery methods that are shown in this article i.e. recovering on a filegroup-by-filegroup basis such that other filegroups remain online while some are still being restored, apply to Enterprise Edition only. For Standard Edition of SQL Server, the database will be in restoring state and won’t be online until the restoration is complete.
Scenario 1: Recovering a corrupt data file while the database remains online
Imagine a situation where you are running a production database on an Enterprise edition of SQL Server and a data file becomes corrupted out of nowhere. Obviously, the data that resides on that corrupt file would not be readable. How do we go about getting the file back on track? SQL Server lets us take the corrupt file offline and perform recovery against it from the backups. You must have the recent backups (Full, Diff and log) for the data file to be recovered point-in-time. The following sections talk about the steps you would need to take to restore and recover the corrupt file so the data on it is back online.
Before we dive into the data file recovery, let's set the environment. In the following code, we are creating a new database called "PiecemealDB" with four filegroups. The Primary, Critical, Average and Old with the Primary being the default. As you can tell, the idea behind the naming convention for filegroups is that the Critical filegroup holds the mission-critical data that has a very low RTO, Average filegroup, on the other hand, has an average RTO and finally, Old filegroup has data that is hardly used. Basically, the data in the Critical filegroup has the highest uptime requirements than the Average and the Old filegroups. Notice that we are creating two data files in the Critical filegroup.
--CREATING THE PIECEMEALDB DATABASE WITH FOUR FILEGROUPS: PRIMARY, CRITICAL, AVERAGE AND OLD AND ONE LOG FILE
USE [MASTER]
GO
CREATE DATABASE [PIECEMEALDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'PIECEMEALDB', FILENAME = N'E:\SQL_DATA\PIECEMEALDB.MDF' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [AVERAGE]
( NAME = N'PIECEMEALDB_AVERAGE', FILENAME = N'D:\SQL_DATA\PIECEMEALDB_AVERAGE.NDF' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [CRITICAL]
( NAME = N'PIECEMEALDB_CRITICAL_1', FILENAME = N'D:\SQL_DATA\PIECEMEALDB_CRITICAL_1.NDF' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
( NAME = N'PIECEMEALDB_CRITICAL_2', FILENAME = N'D:\SQL_DATA\PIECEMEALDB_CRITICAL_2.NDF' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
FILEGROUP [OLD]
( NAME = N'PIECEMEALDB_OLD', FILENAME = N'C:\SQL_DATA\PIECEMEALDB_OLD.NDF' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
LOG ON
( NAME = N'PIECEMEALDB_LOG', FILENAME = N'D:\SQL_LOGS\PIECEMEALDB.LDF' , SIZE = 20032KB , MAXSIZE = 2048GB , FILEGROWTH = 20032KB )
GO
ALTER DATABASE [PIECEMEALDB] SET RECOVERY FULL
GO
Now that the database is created, let us go ahead and create a table within it. Note that we are creating the table in the Critical filegroup leaving the primary and other filegroups empty. However, in the real world, you would have data in all other filegroups depending on the data distribution.
The comments in the code are self-explanatory but the basic idea is to insert a single record and take a different type of backup so to form a restore sequence i.e. Full is followed by Differential, which is further followed by Log and finally, the tail-log. Tail-log backup will be taken prior to the recovery i.e. after the disaster.
USE PIECEMEALDB
GO
--CREATING A TABLE IN THE CRITICAL FILEGROUP AND INSERTING FIRST RECORD
CREATE TABLE MYTABLE (ID INT, NAME VARCHAR(25), COMMENT NVARCHAR(250))
ON CRITICAL
GO
INSERT INTO MYTABLE VALUES (1, 'JOSEPH MARTIN', 'THIS ROW IS INCLUDED IN THE FULL BACKUP')
GO
--FIRST FULL BACKUP
--THIS BACKUP COVERS THE FIRST RECORD
BACKUP DATABASE [PIECEMEALDB] TO DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_FULL.BAK'
WITH NOFORMAT, NOINIT, NAME = N'PIECEMEALDB-FULL DATABASE BACKUP', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
INSERT INTO MYTABLE VALUES (2, 'AMANDA PETERS', 'THIS ROW IS INCLUDED IN THE DIFFERENTIAL BACKUP')
GO
--FIRST DIFFERENTIAL BACKUP AFTER THE FULL BACKUP
--THIS BACKUP COVERS THE SECOND RECORD
BACKUP DATABASE [PIECEMEALDB] TO DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_DIFF.BAK'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'PIECEMEALDB-DIFF DATABASE BACKUP', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
INSERT INTO MYTABLE VALUES (3, 'MOHSIN KHAN', 'THIS ROW IS INCLUDED IN THE LOG BACKUP')
GO
--LOG BACKUP AFTER THE DIFFERENTIAL
--THIS LOG BACKUP COVERS THE THIRD RECORD INSERTED ABOVE
BACKUP LOG [PIECEMEALDB] TO DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_LOG.TRN'
WITH NOFORMAT, NOINIT, NAME = N'PIECEMEALDB-T-LOG BACKUP', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--INSERT A FOURTH RECORD WHICH IS NOT INCLUDED IN ANY BACKUP(We will take a tail-log backup during recovery which would cover this record)
INSERT INTO MYTABLE VALUES (4, 'JAMES MATTHEW', 'THIS ROW IS INCLUDED IN THE TAIL-LOG BACKUP')
GO
Alright, the backups have been done and we have the restore sequence ready to go. At this point, we're waiting for the disaster to strike. Let's assume the data file "PiecemealDB_Critical_1" got corrupted due to an issue with the underlying drive.
The very first step (as indicated in the code sequence below) is to take the affected datafile offline. Remember, the database is still online and the rest of the filegroups are running fine and users are able to query other filegroups' data.
--AT THIS POINT, PRETEND THAT SOMETHING HAPPENED TO THE DATA FILE "PIECEMEALDB_CRITICAL_1" AND IT CRASHED
-- STEP#1: TAKING THE CRASHED FILE OFFLINE
ALTER DATABASE PIECEMEALDB
MODIFY FILE(NAME = PIECEMEALDB_CRITICAL_1, OFFLINE);
GO
-- LET'S CHECK WHAT STATES THE FILES ARE IN
SELECT FILE_ID, NAME, STATE_DESC, PHYSICAL_NAME
FROM PIECEMEALDB.SYS.DATABASE_FILES;
GO
After the data file is taken offline, let's check the status of all files in the database by querying "PiecemealDB.sys.database_files".
Sure enough, the status for "PiecemealDB_Critical_1" changed to "OFFLINE".
Moving on, let's back up the tail of the transaction log so we don't miss the transactions that have yet to be hardened to the data files.
The restore of the data file starts at step 3 below. Notice that in the restore statement we are specifying the word "File" and the name of the data file to be restored from the last known good Full backup. Remember, the database is online at this time with all other filegroups.
-- STEP#2: TAKING THE TAIL-LOG BACKUP
BACKUP LOG [PIECEMEALDB] TO DISK = N'D:\SQL_BACKUPS\PIECEMEAL_TAIL-LOG.TRN'
WITH NO_TRUNCATE, NOFORMAT, NOINIT, NAME = N'PIECEMEALDB-TAIL-LOG BACKUP', STATS = 10
GO
-- STEP#3: RESTORING THE CORRUPT FILE FROM THE FULL BACKUP TO NEW LOCATION. NOTICE THAT WE ARE ONLY RESTORING THE CORRUPT FILE WHICH IS OFFLINE AT THE MOMENT
RESTORE DATABASE [PIECEMEALDB]
FILE = N'PIECEMEALDB_CRITICAL_1'
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_FULL.BAK'
WITH FILE = 1,
MOVE N'PIECEMEALDB_CRITICAL_1' TO N'C:\SQL_DATA\PIECEMEALDB_CRITICAL_1.NDF',
NORECOVERY, NOUNLOAD, STATS = 5;
-- CHECKING THE STATE OF THE FILES
SELECT FILE_ID, NAME, STATE_DESC, PHYSICAL_NAME
FROM PIECEMEALDB.SYS.DATABASE_FILES;
GO
The following output is returned upon successful restore of the file from the full backup ran above. Notice the output displays the name of the file that was restored from this backup. We haven't recovered this data file yet.
At this point, the files are in "Restoring" mode as shown in the screenshot below. Observe that the second data file "PiecemealDB_Critical_2" shows as restoring too when we are only working on the "PiecemealDB_Critical_1" data file. Also, notice that the location for "PiecemealDB_Critical_1" changed to C: drive since we gave that in the restore statement.
At this point, if you try to query the table "MyTable" which was created on the "Critical" filegroup whose data files are being restored, it gives the following error and that is understandable.
Now, let's move on to applying the rest of the backups and bring the data file back online. The Full backup restored prior is followed by the Differential backup and Log backup taken one after the other. At last, apply the tail-log backup. You have the option to use "STOPAT" in the tail-log backup restore to recover to a specific point-in-time but this example doesn't use it.
--AT THIS POINT THE TABLE IS STILL NOT ONLINE BUT WE KNOW THAT ONE FIRST RECORD IS INSERTED VIA FULL BACKUP
--STEP#4: RESTORE DIFFERENTIAL BACKUP. THERE'S NO NEED TO SPECIFY THE FILE NAME. SQL AUTOMATICALLY KNOWS WHICH ONE TO RESTORE
RESTORE DATABASE [PIECEMEALDB]
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_DIFF.BAK WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 5;
--AT THIS POINT THE TABLE IS STILL NOT ONLINE BUT WE KNOW THAT SECOND RECORD IS INSERTED
-- STEP#5: TRANSACTION LOG BACKUP
RESTORE LOG [PIECEMEALDB]
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_LOG.TRN'
WITH NOUNLOAD, NORECOVERY, STATS = 10;
--AT THIS POINT THE TABLE IS STILL NOT ONLINE BUT WE KNOW THAT THIRD RECORD IS INSERTED
-- STEP#6: TAIL-LOG BACKUP. THIS WILL RESTORE THE FINAL TAIL-LOG BACKUP WHICH INCLUDES THE FOURTH RECORD
RESTORE LOG [PIECEMEALDB]
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEAL_TAIL-LOG.TRN'
WITH NOUNLOAD, NORECOVERY, STATS = 10;
--STEP#7: FINALLY, RECOVER THE DATA FILE. THIS BRINGS THE DATA FILE ONLINE
RESTORE DATABASE [PIECEMEALDB] WITH RECOVERY;
GO
--LET US CHECK THE STATE OF THE FILES
SELECT FILE_ID, NAME, STATE_DESC, PHYSICAL_NAME
FROM PIECEMEALDB.SYS.DATABASE_FILES;
GO
At last, the data file is recovered and its status switches to 'online'.
Let's run select against the 'MyTable' table and see if it is fully recovered. Recall we had inserted four records after it was created.
The above screenshot shows that the table was successfully recovered from the backups and that all four records were recovered successfully.
Scenario 2: Recovering a database on a filegroup-by-filegroup basis from the Full, Diff and Log backups
Imagine a situation where the entire database (or instance) crashes and you have to migrate the backups to a remote instance and perform the recovery there. To better understand that situation, let's go back to our previous example of the "PiecemealDB". Imagine the whole database crashes and needs to be recovered on a new instance. The recovery steps would include migrating the backups (Full, Diff and Log) to the target server and start the restore sequence. If the backups are placed in a shared file server and are accessible from the target server, there should be no need to copy them over. You can simply reference the backup locations in the restore commands from the target server.
Let's assume there are different RTO requirements for each filegroup such that the Critical filegroup needs to be back online ASAP while the Average can take a few hours and the Old filegroup can take even days. The beauty of running on Enterprise edition of SQL is that it allows the database to be restored on a filegroup-by-filegroup level such that the important one (Critical FG in our example) can be restored and brought online while the rest are still being restored.
The challenge in this approach would be for the backups to be transferred over to the target instance i.e. if the backups' size is huge, it will take time and thus delaying the recovery time. On the other hand, if your backups are on a file share, that could speed up the recovery.
Important: Note that the primary filegroup and the log file need to be restored first to bring the database online before the remaining filegroups can be restored. As you can imagine, if you have some good amount of data in the primary filegroup, restoring it could potentially take time and thus the rest of the filegroups would have to wait until the primary is done. A better approach is to have very less data, if not totally empty, in the primary filegroup and distribute the data across other filegroups on the basis of importance or usage or RTO requirement. That way, in case of a disaster, the primary filegroup can be restored quickly and the other filegroups can go in quickly without waiting longer.
Now that we have the Full, Differential and Log backups ready to go, let's try to restore the "Critical" filegroup before the Primary and see what happens. As you would expect, the restore fails (See below) as it expects the primary filegroup and the log file to be restored first.
Let's try restoring the Primary filegroup first but without specifying the "PARTIAL" keyword in the restore command. You will see the following error message when you restore the subsequent Differential and Log backups and try to recover it. Because we are restoring filegroup by filegroup, we have to ensure that the keyword "Partial" is mentioned in the restore statement.
Let's move on to the next step and drop the database followed by the partial restore i.e. restoring only Primary, Critical and Average filegroups and NOT "Old" from the last known good Full backup.
Proceed with applying the Differential and Log backups and finally, recover the database. Notice that we are not applying the tail log backup as we are simulating a disaster that ended up the database to be unusable so tail-log was not possible.
--FILEGROUP-BY-FILEGROUP RESTORE
--ASSUME THAT THE SQL SERVER CRASHED AND NEEDS TO BE RECOVERED. YOU DON'T STAND A CHANCE TO TAKE THE TAIL-LOG BACKUP SO THERE'S A POTENTIAL DATA LOSS
--STEP#1: DROP THE DATABASE PRETENDING A DISASTER OCCURRED AND THE SERVER CRASHED
ALTER DATABASE PIECEMEALDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE PIECEMEALDB
GO
--COPY THE BACKUPS I.E. FULL, DIFFERENTIAL AND LOG TO THE NEW SERVER. IF THE BACKUPS ARE IN A SHARED LOCATION ACCESSIBLE BY THE NEW SERVER, YOU CAN REFERENCE THAT LOCATION IN THE RESTORE COMMAND BELOW
--STEP#2: RESTORING THE THREE FILEGROUPS: PRIMARY, CRITICAL AND AVERAGE IN ONE GO EXCEPT THE 'OLD' FILEGROUP FROM THE FULL BACKUP AND MOVING TO ANOTHER LOCATION. YOU NEED AT LEAST THE PRIMARY FILEGROUP AND THE LOG TO BRING THE DATABASE ONLINE (ENTERPRISE EDITION)
RESTORE DATABASE [PIECEMEALDB]
FILEGROUP = 'PRIMARY', FILEGROUP = 'CRITICAL', FILEGROUP = 'AVERAGE'
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_FULL.BAK'
WITH FILE = 1,
MOVE N'PIECEMEALDB' TO N'C:\SQL_DATA\PIECEMEALDB.NDF',
MOVE N'PIECEMEALDB_CRITICAL_1' TO N'C:\SQL_DATA\PIECEMEALDB_CRITICAL_1.NDF',
MOVE N'PIECEMEALDB_CRITICAL_2' TO N'C:\SQL_DATA\PIECEMEALDB_CRITICAL_2.NDF',
MOVE N'PIECEMEALDB_AVERAGE' TO N'C:\SQL_DATA\PIECEMEALDB_AVERAGE.NDF',
MOVE N'PIECEMEALDB_LOG' TO N'C:\SQL_DATA\PIECEMEAL_LOG.LDF',
NORECOVERY, NOUNLOAD, PARTIAL, STATS = 5;
--AT THIS POINT, THE TABLE IS STILL NOT ONLINE BUT WE KNOW THAT ONE FIRST RECORD IS INSERTED AT THIS POINT
--STEP#3: RESTORE DIFFERENTIAL BACKUP. THERE'S NO NEED TO SPECIFY THE FILEGROUPS NAME. SQL AUTOMATIACLLY KNOWS WHICH ONES TO RESTORE
RESTORE DATABASE [PIECEMEALDB]
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_DIFF.BAK
WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 5;
--AT THIS POINT THE TABLE IS STILL NOT ONLINE BUT WE KNOW THAT SECOND RECORD IS INSERTED AT THIS POINT
-- STEP#4: TRANSACTION LOG BACKUP
RESTORE LOG [PIECEMEALDB]
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_LOG.TRN'
WITH NOUNLOAD, NORECOVERY, STATS = 10;
--SINCE WE WERE NOT ABLE TO RUN THE TAIL-LOG BACKUP, ONE RECORD WILL BE LOST.
--STEP#5: RECOVER THE DATABASE
RESTORE DATABASE [PIECEMEALDB] WITH RECOVERY;
GO
--LET'S CHECK THE STATE OF THE FILES. ALL DATA FILES EXCEPT 'OLD' ARE ONLINE
--NOTICE RECOVERY PENDING FOR THE PIECEMEALDB_OLD FILEGROUP
--NEXT STEP, WE WILL RECOVER THE OLD FG
SELECT FILE_ID, NAME, STATE_DESC, PHYSICAL_NAME
FROM PIECEMEALDB.SYS.DATABASE_FILES;
GO
At this point, all the data files in the three filegroups other than "Old" are online. The "PiecemealDB_Old" file shows "RECOVERY_PENDING" which is totally understandable. We only restored the three filegroups so there is no way the "Old" filegroup would show online.
Let's go ahead and restore the "OLD" filegroup using the same Full, Diff and Log backups that were used for the other three filegroups restore.
--NO NEED TO MENTION PARTIAL OR SPECIFY THE LOG FILE IN THIS RESTORE
--STEP#6: RESTORING THE FILEGROUP 'OLD' FROM THE FULL BACKUP
RESTORE DATABASE [PIECEMEALDB]
FILEGROUP = 'OLD'
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_FULL.BAK'
WITH FILE = 1,
MOVE N'PIECEMEALDB_OLD' TO N'C:\SQL_DATA\PIECEMEALDB_OLD.NDF',
NORECOVERY, NOUNLOAD, STATS = 5;
--STEP#7: RESTORING THE FILEGROUP 'OLD' FROM THE DIFF BACKUP
RESTORE DATABASE [PIECEMEALDB]
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_DIFF.BAK'
WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 5;
--STEP#8: RESTORING THE LOG BACKUP
RESTORE LOG [PIECEMEALDB]
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_LOG.TRN'
WITH NOUNLOAD, NORECOVERY, STATS = 10;
At this point, we haven't recovered the Old data file yet so it shows as "RESTORING".
Now that all backups have been applied, let's recover it.
--STEP#9: RECOVERING THE 'OLD' FILEGROUP
RESTORE DATABASE [PIECEMEALDB] WITH RECOVERY;
GO
--CHECKING THE DATA FILES' STATUS
SELECT FILE_ID, NAME, STATE_DESC, PHYSICAL_NAME
FROM PIECEMEALDB.SYS.DATABASE_FILES;
GO
Finally, the PiecemealDB_Old" data file shows online and the recovery is complete.
Remember, we didn't restore the tail-log backup and hence, only three records were restored into the *MyTable *table.
Scenario 3: Recovering a database on a filegroup-by-filegroup basis from the filegroup backups
SQL Server allows taking backups of individual filegroups. That way, you can potentially skip the read-only filegroups and run backups only for the read-write ones. That way, the backups would run faster as they don't have to scan the whole database and instead just work on the specific filegroup being backed up. Also, if there is a disaster and you need to move backups to a different site to recover on a new instance, moving the filegroup backups would be faster than moving the full backups. You can simply move the backups for the read-write filegroups and recover the database so the business can continue and later work on restoring the read-only filegroups.
Back to our previous example, let's assume that the filegroup "Old" contains static historical data which doesn't get updated and can be taken into read-only mode. In the following T-SQL code, we are first turning the "Old" filegroup to read-only and performing a separate backup of it (i.e. Old filegroup). We then proceed to take the backup of the remaining read-write filegroups using the keyword "Read_Write_Filegroups" in the backup command. This would span all read-write filegroups. This is followed by the Differential backup. After all that is done, imagine a disaster struck and we are required to perform recovery in such a way that except "Old" filegroup, remaining need to be brought online as early as possible.
The restore/recovery sequence includes the following steps:
1. Taking the tail-log backup, if at all possible.
2. Restore all three read-write filegroups (Primary, Critical and Average) from the read-write Filegroup backup we took before (make sure to specify the keyword 'Partial')
3. Restore the Differential backup.
4. Restore the tail-log backup.
5. Recover the database and check the state of the files.
--CHANGING THE FILEGROUP 'OLD' TO READ ONLY
USE [PIECEMEALDB]
GO
DECLARE @READONLY BIT
SELECT @READONLY=CONVERT(BIT, (STATUS & 0X08)) FROM SYSFILEGROUPS WHERE GROUPNAME=N'OLD'
IF(@READONLY=0)
ALTER DATABASE [PIECEMEALDB] MODIFY FILEGROUP [OLD] READONLY
GO
USE [MASTER]
GO
DECLARE @READONLY BIT
SELECT @READONLY=CONVERT(BIT, (STATUS & 0X08)) FROM SYSFILEGROUPS WHERE GROUPNAME=N'OLD'
IF(@READONLY=0)
ALTER DATABASE [PIECEMEALDB] MODIFY FILEGROUP [OLD] READONLY
GO
--BACKUP OF READ-ONLY FILEGROUP 'OLD'. THIS WILL BE RESTORED INDEPENDENTLY LATER
BACKUP DATABASE [PIECEMEALDB] FILEGROUP = 'OLD' TO DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_OLD_FILEGROUP_RO.BAK'
WITH NOFORMAT, NOINIT, NAME = N'PIECEMEALDB-OLD FG BACKUP', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- FULL BACKUP OF OTHER READ/WRITE FGS. NOTICE THE WORD "READ_WRITE_FILEGROUPS"
BACKUP DATABASE PIECEMEALDB
READ_WRITE_FILEGROUPS
TO DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_ALL_FILEGROUPS_RW.BAK'
WITH NOFORMAT, NOINIT, NAME = N'PIECEMEALDB-ALL_RW_FG_BACKUP', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--DIFFERENTIAL BACKUP
BACKUP DATABASE [PIECEMEALDB] TO DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_DIFF_DB.BAK'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'PIECEMEALDB-DIFF DATABASE BACKUP', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--AT THIS POINT, PRETEND SOMETHING HAPPENED AND THE DATABASE CRASHED. THE FIRST THING TO DO IS TAKE THE TAIL-LOG BACKUP, IF POSSIBLE
-- STEP#1: TAKING THE TAIL-LOG BACKUP
BACKUP LOG [PIECEMEALDB] TO DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_TAILLOG.TRN'
WITH NO_TRUNCATE, NOFORMAT, NOINIT, NAME = N'PIECEMEALDB-T-LOG BACKUP', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--JUST SO WE HAVE A CLEAN START, DROPPING THE DATABASE
DROP DATABASE PIECEMEALDB;
GO
-- STEP#2: RESTORING ALL THREE FILEGROUPS NAMELY, PRIMARY, CRITICAL AND AVERAGE FROM THE FULL BACKUP WITHOUT OLD FG
-- NOTICE THE PARAMETER "PARTIAL" IN THE RESTORE COMMAND
RESTORE DATABASE [PIECEMEALDB]
FILEGROUP = 'PRIMARY', FILEGROUP = 'CRITICAL', FILEGROUP = 'AVERAGE'
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_ALL_FILEGROUPS_RW.BAK'
WITH FILE = 1,
MOVE N'PIECEMEALDB' TO N'C:\SQL_DATA\PIECEMEALDB.NDF',
MOVE N'PIECEMEALDB_CRITICAL_1' TO N'C:\SQL_DATA\PIECEMEALDB_CRITICAL_1.NDF',
MOVE N'PIECEMEALDB_CRITICAL_2' TO N'C:\SQL_DATA\PIECEMEALDB_CRITICAL_2.NDF',
MOVE N'PIECEMEALDB_AVERAGE' TO N'C:\SQL_DATA\PIECEMEALDB_AVERAGE.NDF',
MOVE N'PIECEMEALDB_LOG' TO N'C:\SQL_DATA\PIECEMEAL_LOG.LDF',
NORECOVERY, NOUNLOAD, PARTIAL, STATS = 5;
-- STEP#3: RESTORE THE DIFFERENTIAL BACKUP
RESTORE DATABASE [PIECEMEALDB]
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_DIFF_DB.BAK'
WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 5;
--AT THIS POINT THE TABLE IS STILL NOT ONLINE BUT WE KNOW THAT SECOND RECORD HAS BEEN RESTORED AT THIS POINT
-- STEP#4: RESTORE THE TAIL-LOG BACKUP
RESTORE LOG [PIECEMEALDB]
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_TAILLOG.TRN'
WITH NOUNLOAD, NORECOVERY, STATS = 10;
-- STEP#5: RECOVER THE DATABASE. ALL THREE FILEGROUPS WOULD BE ONLINE AND DATABASE UP AND RUNNING.
RESTORE DATABASE [PIECEMEALDB] WITH RECOVERY;
GO
-- STATE OF THE FILES
SELECT FILE_ID, NAME, STATE_DESC, PHYSICAL_NAME
FROM PIECEMEALDB.SYS.DATABASE_FILES;
GO
As we only restored and recovered the three read-write filegroups, the "PiecemealDB_Old" file belonging to the "Old" filegroup shows as Recovery Pending, which is totally understandable.
Let's go ahead and recover the read-only filegroup "Old" using the read-only filegroup backup we took at the beginning of this scenario.
--AT THIS POINT THE DATABASE IS FUNCTIONAL WITH ALL READWRITE FGS ONLINE EXCEPT THE READONLY 'OLD' FILEGROUP
--STEP#6: RESTORING THE "OLD" FILEGROUP AND RECOVERING
--NO PARTIAL KEYWORD AND NO LOG FILE IN THE RESTORE COMMAND
RESTORE DATABASE [PIECEMEALDB]
FILEGROUP = 'OLD'
FROM DISK = N'D:\SQL_BACKUPS\PIECEMEALDB_OLD_FILEGROUP_RO.BAK'
WITH FILE = 1,
MOVE N'PIECEMEALDB_OLD' TO N'C:\SQL_DATA\PIECEMEALDB_OLD.NDF',
RECOVERY, NOUNLOAD, STATS = 5;
-- CHECKING THE STATE OF THE FILES
SELECT FILE_ID, NAME, STATE_DESC, PHYSICAL_NAME
FROM PIECEMEALDB.SYS.DATABASE_FILES;
GO
After the read-only filegroup is restored and recovered, all data files show the state of Online.
This completes the third scenario.
Note: if you try to perform the partial restore against a Standard edition of SQL Server, the database will be switched to "Restoring" state and won't be available for use. If you try to access it, you will get the following error. So in case of Standard edition, it makes sense to restore all filegroups as there is little point in restoring partially since the database won't be online until the full restoration is complete.
Conclusion
Enterprise Edition of SQL Server allows you to leverage the partial database availability wherein some part (filegroups) of the database is online and allows the users to continue with their operations while the remaining part is being restored. This feature would be of enormous help when mission-critical data is on a separate filegroup or different filegroups have different SLAs. That way, critical ones can be restored first and the rest can be done after the database is brought online. Partial database backups make the backups run faster and allow filegroup-by-filegroup restore run even smoother. Of course, everything depends on the business requirements, SLAs, RPOs and the RTOs, but the piecemeal restore and partial backup features do give some good flexibility and save the DBAs a lot of time during database recoveries.