Back up files and filegroups

 back up files This article will go over the topic of how to back up files and filegroups in SQL Server using either SSMS or T-SQL queries.
Continue reading in order to find out why and when you should backup files and filegroups.

Let’s say you are in the situation in which your database’s size and performance requirements have rendered making a FULL backup of your database less practical.

At this point, you still have options for doing your backup, but in the form of a file backup instead. A file backup will contain all of the data in one or more files (or filegroups).

Before we start to show you how to do this type of backup we want you to be aware of some limitations and restrictions.

Limitations / Restrictions
    • The BACKUP command cannot be ran in an explicit or in an implicit transaction.
    • Using the simple recovery model, the files that are read/write must all be backed up together.
      • This will help you be sure that the database can be correctly restored to a consistent point in time.
      • Instead of specifying each read/write file or filegroups you can use READ_WRITE_FILEGROUPS option which will back up all of the read/write filegroups in the entire database.
      • If you create a backup with the READ_WRITE_FILEGROUPS option, then that backup is known as a partial backup.
    • The BACKUP DATABASE and BACKUP LOG permissions are default for members of the sysadmin fixed server roles and for the db_owner and db_backupoperator roles.
Recommendations

Every backup operation will add an entry, by default, in the SQL Server error log and an entry in the event log.

If you will be backing up the log very often, these types of messages will accumulate very fast which will result in imense error logs, that will without a doubt make searching and finding other messages very difficult.

In such a case you can stop these types of log entries by using a trace flag, 3226 in case non of your backup scripts are dependant of those entries.

Back up files and filegroups with SSMS

    • Begin by connecting to the corresponding instance of SQL Server Databse Engine and expand the server tree in Object Explorer.
    • Expand the Databases section and right click the database (user or system database), go to Tasks and then Backup.

back up files

    • In the Back Up Database window that appears you can eventually select a different database that you might want to back up.
    • In the Backup type section select one of FULL or DIFFERENTIAL options

back up files

    • In the next section, Backup Component select Files and Filegroups which will prompt the Select Files and Filegroups window.
    • Here, in order to back up files and filegroups just check their corresponding boxes.

back up files

    • In the Name section don’t forget to change the name or leave it as suggested if it is acceptable. Also look over the Description section if necessary.
    • A good practice would be to specify a date or a period when the backup will expire.

back up files

    • Choose the type of destination where to create your backup, Disk or Tape.
    • Set the destination Path for the backup by pressing Add and going to the folder where the backup will be created and specify the backup name.
    • For more advanced features, go to Options page on the top left side of the window.
    • Selecting the Overwrite Media option you will have to select one of the following two options.
      • Back up to the existing media set – which has two suboptions
        • Append to the existing backup set – will append the new backup to this set
        • Overwrite all existing backup sets – will overwrite all sets and just leave your last backup on it
        • Optionally tick the Check media set name and backup set expiration option to force the backup operation to check the date when the set will expire.
        • If no name is specified then a media set with a blank name is created.

back up files

      • Back up to a new media set, and erase all existing backup sets
        • Enter a name in the New media set name box and a description for the media set in the next text box.

back up files

    •  Use the options under the Reliability section to verify the backup when it has finished and to perform a checksum operation before writing to the media.
    • For SQL Server 2008 and later you will have the possibility to use compression methods to lower the size of your backup file.
    • Choose from the dropdown list what option you would like.

back up files

    • After setting up all of the options you find necessary click OK, operation which will create the backup file and hopefully you will be prompted with a message similar to this.

back up files

Back up files and filegroups with T-SQL queries

If you preffer to write queries to do this operation then you have this possibility by using the BACKUP DATABASE command along with a few options specifying that you want to back up files or filegroups, as you will see below a few examples.

Assuming we have a database called TestDatabase which uses the full recovery model and contains the following secondary filegroups:

      • A filegroup named TestDB1 has two files, TestDB_F1 and TestDB_F2
      • A filegroup named TestDB2 has also two files, TESTDB_F3 and TestDB_F4

We will present a few query examples for different cases:

Creating a file backup of two files
BACKUP DATABASE TestDatabase
    FILE = 'TestDB_F1',
    FILE = 'TestDB_F2'
TO DISK = 'Z:\SQL_Server_Backups\TestDatabase\TesTDBFiles.bak';
Creating a full file backup of secondary filegroups
BACKUP DATABASE TestDatabase
    FILEGROUP = 'TestDB1',
    FILEGROUP = 'TestDB2'
TO DISK = 'Z:\SQL_Server_Backups\TestDatabase\TestDBFileGroup.bak';
Creating a differential file backup of secondary filegroups
BACKUP DATABASE TestDatabase
    FILEGROUP = 'TestDB1',
    FILEGROUP = 'TestDB2',
TO DISK = 'Z:\SQL_Server_Backups\TestDatabase\TestDBFileGroupDifferential.bak'
WITH 
    DIFFERENTIAL;

About Radu Gheorghiu

Passionate SQL Developer on the journey of trying to become an expert in all things Data (storage, manipulation, gathering etc.) Information is power, and information can only be obtained from data. Thus, in order to harness the power of information, you must be a master of Data.

Leave a Reply

Your email address will not be published. Required fields are marked *