FileGroup Backup and FileGroup Restore Operations On SQL Server(Partial Backup-Restore)

We perform FileGroup Backup and Restore operations in databases that are usually large and have more than one filegroup, while some of the filegroups are in read only mode. For detailed information about FileGroup and large databases, you can read the article named “How To Create a Database On SQL Server”

For example, you have a database of 30 TB and a partition for each year. You may want to read my article “How To Create Partition On SQL Server“. Each year ‘s partition is placed in a different filegroup. The application only reads and writes on the data of the last 1 year and only reads the data before it. In this case, only 6TB of 30TB is active while the remaining 24TB data never changes.

In such a case, we will set the filegroup as read only as described in the article below and we will get the backup only once.

“How To Set Filegroup To Readonly”

In Backup job, we only get backup of read-write filegroups and decrease backup size and shorten backup time. This type of backup is called Partial Backup. In the Partial backup, you can optionally backup one or more read only file groups.

In filegroup backup and restore operations, the primary filegroup is the most important file group. Because you cannot restore other read write filegroups without restoring the primary filegroup.

Let’s continue through our example;

The database has 1 primary filegroup by default. Suppose we have 1 read write filegroup and 1 read only file group.

We will only get the backup of Read only filegroup only once.

In backup jobs we will only get backup of primary and read-write filegroups.

In our example, we will first get the backup of the database’s primary and read write filegroups and then the backup of the read only filegroups.

We will then restore the backup of only the primary and read write filegroups without using read only backup. In this way, the cost of the backup of read only file groups will be eliminated. And our backup time will be shortened. At the same time, our restore time will be significantly shortened.

With the help of the following script, we can get the backup of all read-write filegroups.

Using SSMS as follows, we can also take backups by specifying all of the read write filegroups one by one.

First, right click on the database and click Tasks and Backup.

We click on “Files and filegroups” in the Backup components section on the screen and we see a screen as follows. In the following screen, because our file groups PRIMARY and TABLESGROUP are read write file groups, we choose both and click ok.

We select Disk from the “Back up to” section at the bottom, and click Add to select the path we will get backup and write the backup name and click ok.

Then we click on the script that says above, and we get the script of the selections we made. A script similar to the above script will appear. Then we run this script.

We get the backup of Read Only file group in the same way.

Restore Operation;

We will restore only read-write filegroups without restoring read only filegroups. If you are using a full recovery model, you must also restore the log backups you have restored.

Before starting Restore, we need to take a tail log backup, or we need to overwrite the existing database by using the replace command to restore it. We’ll start our process by taking the tail log. To get a Tail log backup, read the article “Tail Log Backup“.

We got Tail Log Backup. We are using the following script to restore read write file groups.

If you have done the restore database operation with the WITH REPLACE command without Tail Log Backup and we do not have a log backup, we can recover the database as follows.

If you have log backup after read-write filegroup backup, you need to restore log backups with WITH NORECOVERY by using the following script in order.

Since we have two log backups in the above script, we have restored both with WITH NORECOVERY.

Finally, we recover the database by using the following script with WITH RECOVERY.

I mentioned the backup types in the article “Backup Restore On SQL Server“. We can also use Differential Backup for filegroup backup.

We need to add DIFFERENTIAL as follows to get Differential Backup.

After restore, we need to run the following script to see that all of the filegroups are online.

In this article, we have restored the primary and read write file groups on an existing database.

In the article named “PieceMeal Restore“, we will first restore the backups of primary and readwrite filegroups to another database.

Then, when the application is running on active filegroups, we will restore the read only filegroups.