SQL Server Backup

What Is Backup ??

Backup refers to making a copy of data, such as a database. Backing up your MS SQL Server database is essential for data protection. MS SQL Server backups are generally divided into three types: Full, Differential, and Incremental.

Backup Types:

Following are the most common types of backups available in SQL Server:

1. Full
2. Differential
3. Transaction log
4. Tail Log backup

There are other backup types available as well:

1. Copy-only backup
2. File backups
3. Partial backups.
4. Mirror backups.

Let's Start getting an Overview of the main four backups.

1. Full/Complete: These backups create a complete backup of your database and also a part of the transaction log. This ensures that in the event of a database failure, you can recover the database successfully.

A full backup can be completed either using T-SQL or by using SSMS. The following examples show you how to create a full backup.

T-SQL

An extension ending in .BAK is used to indicate that the backup is a full database backup.

BACKUP DATABASE <Database Name> TO DISK = 'Path of Disk\.bak'
GO

Using SQL Server Management Studio

• Right-click on the database name
• Select Tasks > Backup
• Select “Full” as the backup type
• Select “Disk” as the destination
• Click on “Add…” to add a backup file and type “C:\AdventureWorks.BAK” and click “OK”
• Click “OK” again to create the backup

2. Differential Backup: Create “Differential” backups as an additional option to help with your recovery. A “Differential” backup is a backup of any extent that has changed since your last “Full” backup was created.

T-SQL

BACKUP DATABASE <Database Name> TO DISK = 'Path of Disk\.BAK' WITH DIFFERENTIAL
GO

Using SQL Server Management Studio

• Right-click on the database name
• Select Tasks > Backup
• Select “Differential” as the backup type
• Select “Disk” as the destination
• Click on “Add…” to add a backup file and type “C:\AdventureWorks.BAK” and click “OK”
• Click “OK” again to create the backup

3. Transaction log: Transaction log backup contains changes from the last database backup to that point. Transaction logs, also called T-logs, contain the records of all the changes made to a database, or to its structure. Each time there is a change made to the database, a log record is added to it.

T-SQL

An .TRN extension is commonly used for identifying that the backup is a transaction log backup.

BACKUP LOG <Database Name> TO DISK = 'Path of disk\.TRN'
GO

SQL Server Management Studio

• Right-click on the database name
• Select Tasks > Backup
• Select “Transaction Log” as the backup type
• Select “Disk” as the destination
• Click on “Add…” to add a backup file and type “C:\AdventureWorks.TRN” and click “OK”
• Click “OK” again to create the backup

4. Tail log Backup: Tail log backup and transaction log backup are the same things, the only difference is that when you take tail log backup, the database goes into non-operational mode. A tail-log backup captures any log records which has not yet been backed up by the last transactional log backup.

T-SQL

An .TRN extension is commonly used for identifying that the backup is a transaction log backup.

BACKUP LOG <Database Name> TO DISK = 'Path of disk\.TRN' with no_truncate,norecovery
GO

Microsoft recommends you to Perform a TAIL LOG backup during the following scenario:-

• The database is currently ONLINE and you are planning to perform the restore operation on the database then begin by backing up the tail of the log. To avoid any error for an ONLINE database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.
• If a database is OFFLINE and fails to start and you need to restore the database, first back up the tail of the transaction log file. Because no transactions can occur during such time, it is optional to use WITH NORECOVERY during such times.
• If a database is DAMAGED, then try to take a tail-log backup by using WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.