SQL Server Database File and Filegroups and Backup

What is SQL Server database filegroups

Every database has a primary file group. SQL Server Database filegorup contain both primary as well as secondary data files. On the other hand we can say that, a combination of data files used to simplify the database administration is called Database Filegroups.

Types of SQL Server Database Filegroup:

1. Primary File Group
2. User Defined File Group

SQL Server Database Files

SQL Server Database has three types of files listed as below:

1. Primary Data File (.mdf) – MDF file contains all the information about the database. It also points to the other files in the database.
2. Secondary Data File (.ndf) – NDF files are user defined files and also called the optional file that is used to store the user data.
3. Transaction Log (.ldf)– The transaction log files are those files that contains all the log information related to the database.

Backup SQL Server Database File and Filegroups

Here we will discuss how to backup the SQL Server database file and filegroups using SQL Server Management Studio and Transact-SQL.

Via SQL Server Management Studio

In SSMS you need to follow the given steps to perform the backup of SQL Server database file and filegroups.

1. Connect to the proper SQL Server instance and expand the databases.
2. Select a user database or system database.
3. Right click on the database and from the drop down menu > move the cursor to Task and then select Backup (backup dialog box will appear).
4. Verify the database name and select backup type: Full or Differential.
5. Now in Backup Component option, select the File and Filegroups.
6. From Select File and Filegroups window, select the file and filegroups that you want to backup. You can select multiple files and file groups for the backup.
7. Now you can type a backup set name as per your convenient or choose the default backup set name. You can also enter the description in the description box of the backup set (optional).
8. In the next step you need to set the expiry of the backup set

     a. To have the backup set expire in a number of days click on After. You can enter the number of days from 0 to 99999. ‘0’ means the backup will never expire.
     b. To expire the backup on a specific date, click ON and enter the date.

9. Choose the destination of backup by clicking on Disk or Tape.
10. Now you need to select the media option to Overwrite

     a. You can choose the backup to the existing media set.
     b. Or backup to a new media set, and erase all the existing database backup sets.

11. In the reliability section, check (optional)

     a. Verify backup when finished
     b. Perform checksum before writing to media or continue on checksum error.

12. The Unload the tape after backup option is active, if you are backing up to a tape drive. You can enables the Rewind the tape before unloading option by clicking this option.

Via Transact-SQL

You can use the T-SQL query to backup the file and filegroups in SQL Server Database.

Creating a Differential Backup

BACKUP DATABASE database_name
     FILEGROUP = 'filegroups_name1',
     FILEGROUP = 'filegroupsname_2'
     TO DISK = 'E:\MySQLServer\Backups\database_name\filegroups.bck';
     With
       Differential
   GO

Creating a Full Backup

BACKUP DATABASE database_name
     FILEGROUP = 'filegroups_name1',
     FILEGROUP = 'filegroupsname_2'
     TO DISK
    GO

Conclusion

In this article we have discussed about what are the SQL Server file and filegroups and how to perform the backup operation on them using SQL Server Management Studio and Transact-SQL.

Read this article to know how to restore file and filegroup in SQL Server from backup.