Database Backup in SQL Server 2005 : Part III


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.

image

Now Lets see the file details of the database SKG_TEST_FG (Database Properties –> Files).

image

image

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.

image

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.

image

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.

image

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.

image

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.

image

In the Option page of Back up Database, select to Append the backup files to the existing media set.

image

Click on Ok to start the filegroup backup for the database SKG_TEST_FG.

image 

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.

image

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.

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s