In the previous two posts, we discussed how to create a Full, Differential and Transactional-log backup of a database. We have also discussed that transactional-log backup is not supported when a database is using Simple recovery model. However, in case of a large OLTP (Online Transaction Processing) database, sometimes it is not possible to create a full database backup. Because of the large size of the database, the full backup of the database requires more times. So if the database consists of multiple filegroup and is using Full / Bulk – Logged Recovery model, it is possible to backup each filegroup individually. Let’s see how filegroup backup is created using SSMS.
Creating Filegroup Backup using SSMS
To create the filegroup backup, we are going to use SKG_TEST_FG database. Let’s see the database properties of SKG_TEST_FG.
Now Lets see the file details of the database SKG_TEST_FG (Database Properties –> Files).
Now, since our database is ready, let’s populate some data inside the database. I have created a custom script to create some data for SKG_TEST_FG.
Using the above script, we have created two tables, one in CustFG and one in Primary filegroup. After that, we have inserted some rows into the tables. Once all the required data is inserted, both the tables will have the following rows as shown in the below screen-shot.
Once we have inserted the sample data, we will initiate a full database backup. After the full backup is completed, we will initiate the filegroup backup.
All the filegroup backup files should be on the same media set. Before creating the backup for CustFG, we will add some more data in the dbo.testtable1. Once the data has been added we will create a filegroup backup for the CustFG filegroup.
From the Files & Filegroup option, select CustFG filegroup to create the filegroup backup of SKG_TEST_FG database. Make sure, we are using the same media set, on which we have the earlier full backup file.
In the Option page of Back up Database, select to Append the backup files to the existing media set.
Click on Ok to start the filegroup backup for the database SKG_TEST_FG.
Enter some additional sample data for table ‘dbo.testtable2’ on Primary filegroup. Also, create 2nd differential filegroup backup on the same media set. Once we create the 1st diff backup of the Primary filegroup, we will get the following screen.
After the differential filegroup backup, we will create the transaction log backup. This will complete the process of filegroup backup for the above database.
In our next post, we will discuss how to create the database backup using Transact-sql query language. With my next post, I am going to complete my four-part series of Database Backup in SQL Server 2005.