Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In the article named “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)“, I explained the issue that we can restore only read-write filegroups without restoring the filegroup in read only mode by taking the backup of read-write filegroup.
In this article we will restore the filegroups one by one. But we will recover the database without waiting for all filegroups to finish restoring.
For example, you have a database of 50 TB. After restoring only the last file group, we can make the database available without waiting for the rest of the filegroup to be restored. Of course there are some conditions to do this. We will examine these conditions later in this article.
First, we get the backup of the primary and all read-write filegroup as described in the article “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)“, and then we get the backup of read only filegroups.
In the article “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)“, we did not restore read only filegroups, and as a result of the restore process, read only filegroups were also online. In this article, we’ll first restore all read write filegroups, but read only filegroups will remain in recovey pending mode.
We get the backup of the primary and all read write filegroups with the following script.
1 2 3 | BACKUP DATABASE [Test] READ_WRITE_FILEGROUPS TO DISK = N'C:\MSSQL\READWRITEFILEGROUPS.BAK' WITH NOFORMAT, NOINIT, NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 1 |
With the following script, we get the backup of read only filegroup.
1 2 3 | BACKUP DATABASE [Test] FILEGROUP = N'READONLYFILEGROUP' TO DISK = N'C:\MSSQL\READONLYFILEGROUPBACKUP.BAK' WITH NOFORMAT, NOINIT, NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 1 |
Then, with the following script, we perform the restore process in recovery mode by specifying the PRIMARY and TABLESGROUP filegroups one by one. Notice that unlike the restore script in the article “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)“, PARTIAL was added and the filegroups were specified one by one. If you have a log backup that you will restore, you can restore the log backups as described in the article “FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)” by making the RECOVERY part in the following script NORECOVERY.
1 2 3 4 5 6 7 | use master GO RESTORE DATABASE Test FILEGROUP='PRIMARY' ,FILEGROUP='TABLESGROUP' FROM DISK = N'C:\MSSQL\READWRITEFILEGROUPS.BAK' WITH PARTIAL,RECOVERY; GO |
After this restore is complete, read-write filegroups will be accessible, but you cannot access the read only filegroup because it is in recovey pending mode.
With the help of the following script, you can see that the read only filegroup is waiting in recovey pending mode.
1 | SELECT [name], [state_desc] FROM Test.sys.database_files; |
With the help of the following script, you can complete the process by restoring the read only filegroup.
1 2 | RESTORE DATABASE Test FILEGROUP='READONLYFILEGROUP' FROM DISK = N'C:\MSSQL\READONLYFILEGROUPBACKUP.BAK' WITH RECOVERY |
When you run the following script again, you will see that read only file group is also online.
1 2 3 4 5 | SELECT [name], [state_desc] FROM Test.sys.database_files; GO |
