If you are just beginning SQL Server database administration, then you might not be familiar with the different types of database backups that are available and exactly what they backup. This post is going to outline the different types of backups and specifically what they backup, but keep in mind that your backup plan should be based on your recovery needs. You must consider RTO, recovery time objective, and RPO, recovery point objective, when considering the type and frequency of your backups. Another important consideration is the database recovery model, simple, bulk logged, or full. The recovery model will dictate when and how a transaction log is truncated as well as if a transaction log backup can be taken and if a database can be restored to a point in time.
Full Database Backup
The first type database backup that almost everyone is familiar with is a full backup. When a full database backup is taken, then all data files, file groups, and transaction logs are backed up. A full database backup provides the ability to restore the database to the state that it was at the time of the backup. Quite often, I will see posts in the forums that ask why a database backup is 20GB while the database is 50GB and backup compression was not used. A native full database backup will backup the actual data, but not empty data pages. This means that although the database is 50GB, 30GB is unused space. When the backup is restored, the backup will request the full file sizes, but it does not write empty data pages.
In simple recovery model, then each full database backup is autonomous and no log file backup needs to be done, and in fact cannot be done. In simple recovery, the log file is truncated upon checkpoint thereby keeping the log file a manageable size. In full recovery or bulk logged recovery, the log file WILL ONLY BE TRUNCATED WITH A TRANSACTION LOG BACKUP. The only exception to this is for a database that is in full or bulk logged recovery model that has not been backed up, see pseudo-simple post.
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE BackThisUp
END;
GO
CREATE DATABASE BackThisUp
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’BackThisUp’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , _
SIZE = 2048KB , FILEGROWTH = 10%);
GO
–Fulldatabase backup
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpFull.bak’
GO
Every database will have a primary file group which will contain the primary data file. Consider a file group a folder that can contain zero to many data files. Tables and indexes are created on file groups and the file group has data files that these objects are then stored on. Consider a large database that contains huge amounts of historic data as well as current data that is constantly changing. The use of file groups allows you to create tables that will contain historic data on separate physical disks than tables that contain the current data. This helps to separate your high disk reads on one disk and high disk writes on another. For a backup strategy, you may have identified that the historic data only changes at the end of the year when the past years' data is migrated from the current file group to the historic file group. A file group backup will back up ALL the files contained in the file group. If you are using file group back ups as a means of a granular backup and restoration, keep in mind that you MUST have a backup of the Primary file group. If the primary file group becomes corrupt and you do not have a backup to restore it, then the database will not be recoverable (there are ways that you can work around this, but it goes beyond the scope of this post).
File group backups can be taken from a database with a simple, bulk logged, or full recovery model. The transaction log growth and truncation are the same as noted in the full recovery model. File group backups can be run with or without a pre-existing full backup.
–FILEGROUP BACKUP
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE BackThisUp
END;
GO
CREATE DATABASE BackThisUp
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’BackThisUp’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , _
SIZE = 2048KB , FILEGROWTH = 10%);
GO
–Backup the Historic filegroup
BACKUP DATABASE BackThisUp
FILEGROUP = ‘Historic’
TO DISK = ‘C:\Backup\BackThisUpHistoric.bak’
GO
Every SQL Server database will contain a minimum of one data file, which has a default file extension of “.mdf” and a log file, which has an extension of “.ldf“. The primary data file contains all of the system objects of that database and can contain user defined objects. Additional files can be added to file groups, again to distribute disk IO or to provide a granular means of backup and recovery. The only difference between file group backup and file backs ups is that the file backup will backup only the file specified while file group backups will backup all files contained in the file group.
File backups can be taken from a database with a simple, bulk logged, or full recovery model. The transaction log growth and truncation are the same as noted in the full recovery model. File backups can be run with or without a pre-existing full backup.
–FILE BACKUP
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE BackThisUp
END;
GO
CREATE DATABASE BackThisUp
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’BackThisUp’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , _
SIZE = 2048KB , FILEGROWTH = 10%);
GO
–Backup the files in the SalesGroup1 secondary filegroup.
BACKUP DATABASE BackThisUp
FILE = ‘BackThisUpHistoric’
TO DISK = ‘C:\Backup\BackThisUpHistoricFile.bak’
GO
A differential backup backs up all changes in the database since the last FULL backup. If a full database backup has not been taken, then the differential will fail. Keep in mind that the differential backup records all changes since the last FULL backup so if a full backup is taken at midnight and a differential is taken at 0100, 0200, 0300, and 0400, the differential backup taken at 0400 will contain everything that the 0100, 0200, and 0300 have. The benefit of this is that during restoration, the only steps necessary is to first restore the full backup, then only the most recent differential backup.
Differential backups can be taken from a database with a simple, bulk logged, or full recovery model. The transaction log growth and truncation are the same as noted in the full recovery model as differential requires a full backup.
–DIFFERENTIAL Backup
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE BackThisUp
END;
GO
CREATE DATABASE BackThisUp
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’BackThisUp’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , _
SIZE = 2048KB , FILEGROWTH = 10%);
GO
–Differential Backup
–THIS WILL FAIL WITH NO PREVIOUS FULL BACKUP
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpDiff.bak’
WITH DIFFERENTIAL;
GO
–A Full database backup must first be taken before the differential
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpFull.bak’;
GO
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpDiff.bak’
WITH DIFFERENTIAL;
GO
A transaction log backup backs up the active portion of the transaction log. The database recovery model must be set to full or bulk logged and a full, file, or file group backup must first have been taken. After a transaction log backup is taken, the transaction log will be truncated. If you are in bulk logged or full recovery model and you do not backup the transaction log, then THE LOG WILL CONTINUE TO GROW!! The higher the transactions, the more often you should back up the transaction log to ensure recovery and keep the log file size in check.
For the accidental dba, this is a VERY common issue to have unmanageable log files due to not having scheduled transaction log backups, but I have also run into dbas that know better and have honestly said “it’s just disk space“. REALLY!!! C’MON MAN!!
–LOG Backup
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE BackThisUp
END;
GO
CREATE DATABASE BackThisUp
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’BackThisUp’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , _
SIZE = 2048KB , FILEGROWTH = 10%);
GO
–Log Backup
–THIS WILL FAIL WITH NO PREVIOUS BACKUP
BACKUP LOG BackThisUp
TO DISK = ‘C:\Backup\BackThisUpLog.trn’;
GO
–A database backup must first be taken before the log
BACKUP DATABASE BackThisUp
FILEGROUP = ‘Historic’
TO DISK = ‘C:\Backup\BackThisUpFileGroup.bak’;
GO
BACKUP LOG BackThisUp
TO DISK = ‘C:\Backup\BackThisUpLog.trn’;
GO
The tail log backup is a transaction log backup, HOWEVER it is the backup of the transaction log that is taken before beginning the restoration process. The syntax is identical to a transaction log backup, except the clause WITH NORECOVERY is added that tells SQL that the database is about to be restored. It requires that exclusive access to the database as the database will be placed in recovery. The tail log backup gets all of the changes since the last log backup and provides the ability to restore the database to a point in time. Just like a transaction log backup, a tail log backup requires that the database be in full or bulk logged recovery model.
One question that often arises is that if the database becomes corrupt or suspect, how can the tail log be backed up. The answer is that SQL only deeds access to the transaction log file. If the file has not become corrupt, then the backup can be taken.
–TAIL LOG Backup
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE BackThisUp
END;
GO
CREATE DATABASE BackThisUp
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’BackThisUp’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , _
SIZE = 2048KB , FILEGROWTH = 10%);
GO
–A database backup must first be taken before the log
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpFull.bak’;
GO
BACKUP LOG BackThisUp
TO DISK = ‘C:\Backup\BackThisUpLog.trn’
WITH NORECOVERY;
GO
A copy only backup is a full database backup EXCEPT that IT is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. So, why would this type of backup be used. I have used this quite often when a developer calls for a copy of the database from production and it must be current as of this minute. If I perform a full database backup, that will affect my restoration steps. Instead, I will perform a cull database backup with COPY ONLY so that my restoration process will remain unchanged.
–COPY ONLY Database Backup
IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘BackThisUp’)
BEGIN
DROP DATABASE BackThisUp
END;
GO
CREATE DATABASE BackThisUp
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’BackThisUp’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp.mdf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N’BackThisUpCurrent’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpCurrent.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB ),
FILEGROUP [Historic]
( NAME = N’BackThisUpHistoric’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUpHistoric.ndf’ , _
SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’BackThisUp_log’, _
FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\BackThisUp_log.ldf’ , _
SIZE = 2048KB , FILEGROWTH = 10%);
GO
–Fulldatabase backup
BACKUP DATABASE BackThisUp
TO DISK = ‘C:\Backup\BackThisUpFull.bak’
WITH COPY_ONLY
GO
Database Locks During Backups
This is a myth. SQL does not place any locks on the database during a backup. This misconception most often arises because you may see degradation in performance during a back up. This is due to disk IO, the reading of the files and writing to the backup files, and not due to locking.
The sample script above can be downloaded here.
David retired as a Sergeant with the Cape Coral Police Department after 22 years of service. His final 10 years of duty were as a database administrator and developer in the Administrative Services Division. He began his career with the police department in 1990 in the patrol division and worked various assignments until being promoted to Sergeant in 1998. Based on his education and experience David was assigned to Administrative Services in 2002 and was responsible for database administration, software integration, and development for public safety. David’s primary focus and expertise is with SQL Server, reporting services, integration services, and analysis services, and he was recognized for his work by SQL Server Magazine as “Innovator of the Year” runner up in 2007. David is an MCITP for SQL Server 2005 and 2008 in both database administration and business intelligence and is a Microsoft Certified Trainer. He regularly posts on the MSDN SQL Server forums where he also serves as a moderator, and is a contributor at SQLCLR.net. In addition to his knowledge of SQL Server David works as a software developer using VB.net and C# and has worked extensively in SharePoint development.
Submit an article or tip
