Understanding SQL Server Backups

SQL Server Backups are the dump of its databases that can be used to recover them in case of any corruption, outage or emergency. Backups are essential for data protection. We run SQL Server backups to safeguard our databases from user errors, media failure, hardware failure etc. There are multiple types of backups available in SQL Server.

  • Full Backup
  • Differential Backup
  • Transaction Log Backup
  • Copy-Only Backup
  • File Backup
  • Partial Backup

Full Backup

Full database backup captures dump of whole database including transaction logs. Full backup is supported in all three recovery models. Below is T-SQL script that can be used to run a full backup of SQL Server database.

-- Back up the TechYaz database to new media set.
  BACKUP DATABASE TechYaz
  TO DISK = 'D:\Backups\TechYaz_25Aug2017.bak'
  WITH FORMAT;
  GO

Differential Backup

A differential backup depends on the most recent full database backup. A differential backup captures only the data that has changed since that full backup. If your database does not have any full backup yet, you will not be able to run its differential backup. A base full backup is required to run the differential backup. Differential backup is faster because it does not capture full database dump but only the extents that are updated since last full backup.  Database engine reads details about updated extents from Differential Changed Map page type and capture those page ids only into the backup.  Read more about these page types on attached article. Differential backup can be used in all recovery models. Below is T-SQL script that can be used to run a differential backup of SQL Server database.

-- Create a full database backup first by running above commands.
  -- Create a differential database backup
  BACKUP DATABASE TechYaz
  TO DISK= 'D:\Backups\TechYaz_25Aug2017.drn'
  WITH DIFFERENTIAL;
  GO

Transaction Log Backup

Transaction log backup works only in full and bulk-logged recovery model. We cannot run log backup in simple recovery model. We must have at least a full database backup to run transaction log backup. If full backup is not performed since the database creation, log backup cannot be executed. We should frequently run log backup to minimize work loss exposure and to truncate the transaction log that make free space in database log file. Run below T-SQL code to run log backup.

-- Create a full database backup first.
  -- Create a log database backup by running below command.
  BACKUP LOG TechYaz
  TO DISK = 'D:\Backups\TechYaz_25Aug2017.trn'
  GO

Copy Only Backup

A copy-only backup is independent of the sequence of conventional SQL Server backups. It is just like conventional full backup without affecting the sequence of existing backup chain. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose. There are two types of copy-only backups.

  • Copy-only full backup
  • Copy-only transaction log backup

You cannot use copy-only full backup as a differential backup base. It means you cannot run differential backup considering copy only full backup as its base neither you can restore any differential backup on top of copy only full backup. A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. The transaction log is never truncated after a copy-only backup.
Copy-only backups are recorded in the is_copy_only column of the backupset table. Below T-SQL can be used to run copy only backups.

--Run Copy-Only Full backup
  BACKUP DATABASE TechYaz
  TO DISK = 'D:\BACKUP\TechYaz_25Aug2017_Copy.bak'
  WITH COPY_ONLY;

--Run Copy-Only Log backup
  BACKUP LOG TechYaz
  TO DISK = 'D:\Backup\TechYaz_25Aug2017_LogCopy.trn'
  WITH COPY_ONLY;

File Backup

As per Book Online, A full file backup backs up all the data in one or more files or filegroups. The files in a SQL Server database can be backed up and restored individually. Also, you can specify a whole filegroup instead of specifying each constituent file individually. Note that if any file in a filegroup is offline (for example, because the file is being restored), the whole filegroup is offline and cannot be backed up. A file backup can serve as the differential base for differential file backups. File backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database.

--Backup the files in the TechYaz_FG secondary filegroup.
  BACKUP DATABASE TechYaz
  FILE = 'TechYaz_Data',
  FILE = 'Techyaz_Data1'
  TO DISK = 'D:\Backups\TechYaz_25Aug2017_filebck.bak';
  GO

--Back up the files in TechYaz_FG filegroup.
  BACKUP DATABASE Sales
  FILEGROUP = 'TechYaz_FG'
  TO DISK = 'D:\Backups\TechYaz_25Aug2017_filegrp.bak';
  Go
--Back up the files in TechYaz_FG filegroup.
  BACKUP DATABASE Sales
  FILEGROUP = 'TechYaz_FG'
  TO DISK = 'D:\Backups\TechYaz_25Aug2017_filegrp.drn';
  With DIFFERENTIAL

Partial Backup

A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. A partial backup includes all the read/write files in a database: the primary filegroup and any read/write secondary filegroups, and also any specified read-only files or filegroups. A Partial backup can be issued for either a Full or Differential backup. This can not be used for Transaction Log backups. Partial backups are not supported by SQL Server Management Studio or the Maintenance Plan Wizard.

--Backup Partial Full Backup
  BACKUP DATABASE TechYaz READ_WRITE_FILEGROUPS
  TO DISK = 'D:\Backups\TechYaz_25Aug2017_Partial.bak';
  GO

--Backup Partial Differential Backup
  BACKUP DATABASE TechYaz READ_WRITE_FILEGROUPS
  TO DISK = 'D:\Backups\TechYaz_25Aug2017_DFNPartial.drn';
  WITH DIFFERENTIAL
  GO

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

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