SQL Server -- Different BACKUP Methods

If you know SQL Server, you probably know there are different types of backups that you can perform.  In this post I will briefly reference each backup type, and show you how to perform each backup using tSQL.

These are the different backups that I will cover:

• Full
• Differential
• Transaction log
• File 
• Filegroup
• Partial
• Copy-Only

Full Backup - I think it's safe to say that this is the most common backup type.  The Full backup creates a complete backup of your database, to include all files and filegroups. This can be used for complete database restoration.   This is the tSQL statement you will use:

       BACKUP DATABASE DBName

       TO DISK = 'C:\MSSQL\Backup\DBName.BAK'

       GO

Differential Backup - This differential, or 'diff' as is commonly said, is a backup of changes that have occured since the last full backup was created.  This is the tSQL statement that you will use:

      BACKUP DATABASE DBName 

      TO DISK = 'C:\MSSQL\Backup\DBName.DIF' WITH DIFFERENTIAL

      GO

Transaction Log Backup - This is only applicable if your database is set to Full or Bulk-Logged Recovery Model. Using the Transaction Log backup with the Full backup will allow you to create a point in time restore. This is the tSQL statement that you will use:

     BACKUP LOG DBName 
     TO DISK = 'C:\MSSQL\Backup\DBName.TRN'
     GO

File Backup - This backup type allows you to backup each database file independently. This only applies if you have multiple data files, which is commonly done to allow for better performance, by spreading your database objects across multiple filegroups, which exist on different disk allocations. In this example, our DBName database has two data files, and this is the tSQL statement that you will use:

    BACKUP DATABASE DBName 

    FILE = 'DataFile1' 

    TO DISK = 'C:\MSSQL\Backup\DBName_DataFile1.FIL'

    GO

    BACKUP DATABASE DBName 

    FILE = 'DataFile2' 

    TO DISK = 'C:\MSSQL\Backup\DBName_DataFile2.FIL'

    GO

FileGroup Backup - Similar to the File level backup, we can also do FileGroup backups. Only applicable, of course, if you have multiple filegroups, but this allows you to target all objects on the given filegroup.  I, personally, have used this before in a horizontally partitioned database. We had a handful of Read-Only filegroups, containing historical data that never changed. These were ignored in the backup routine, which helped preserve resources by targeting only the Read-Write filegroups. This is the tSQL statement that you will use:

    BACKUP DATABASE DBName

    FILEGROUP = 'DBName_HistoricalData' 

    TO DISK = 'C:\MSSQL\Backup\DBName_HistoricalData.FLG'

    GO

Partial Backup - The Partial backups can also be used to ignore Read-Only filegroups.  As the name implies, this will allow you to partially backup the database.  This is the tSQL statement that you will use:

    BACKUP DATABASE DBName READ_WRITE_FILEGROUPS
    TO DISK = 'C:\MSSQL\Backup\DBName_Partial.bak'
    GO

Copy-Only Backup - A Copy-Only backup is very much like creating a 'snapshot' of your database.  It is completely independent of, and will not impact your backup strategy. This is the tSQL statment that you will use:

    BACKUP DATABASE DBName

    TO DISK = 'C:\MSSQL\Backup\DBName_CopyOnly.bak'   

    WITH COPY_ONLY

    GO

That's about it.  Please take a look at my other post for the different RESTORE methods.