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 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
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;
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
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
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018