Commonly used Backup in SQL Server

Rohith Peyaru

Aug, 2023

1. Full backups

A full backup is like making a complete copy of everything in a database, including tables, procedures, and other stuff. It's like taking a snapshot of the entire database at a certain time.

When you do a full backup, you also save a part of the transaction log, which is like a record of recent changes. This helps in case you need to fix something later. So, with a full backup, you can easily put your database back to exactly how it was when you made the backup.

Think of a full backup as the main building block for all other backup plans. You need to do a full backup first before doing any other kinds of backups. It's like creating a strong foundation for keeping your data safe.

How to create full database backup using T-SQL:

{			

			BACKUP DATABASE [AdventureWorks2019]

			TO DISK = 'D:\Backup\ AdventureWorks2019_FullBackup.bak'

		WITH FORMAT, INIT;
			}

How to create full database backup using SQL Server Management Studio (SSMS):

1. In the Object Explorer, right-click on the database you want to back up.

2. Navigate to Tasks > Back Up... This opens the "Back Up Database" wizard.

3. Select "Full" as the backup type.

2. Differential Backups

A differential database backup is like a smart update to the last full backup. It includes all the changes made since that full backup. If you've made only a few recent changes, the differential backup can be small. But if there are lots of changes, the differential backup can get big.

Unlike a full backup that saves everything, a differential backup is quicker because it only focuses on what's changed. It captures the state of the changes at the time of the backup. If you do a bunch of differential backups, each one might have different data because they capture new changes each time. But, these backups can get larger over time, making the restore process take longer.

To keep things efficient, it's a good idea to do a new full backup every so often. This resets the baseline for the differential backups and helps manage their size.

Differential backups are a space and time saver, but they grow as your data changes. If they get too big, they lose their speed advantage. Restoring a large differential backup might need the full backup first, then the differential one. Generally, you'd restore the most recent full backup and then the most recent differential backup that's based on it.

How to create Differential database backup using T-SQL:

{			

		BACKUP DATABASE [AdventureWorks2019]

		To DISK= 'D:\Backup\ AdventureWorks2019_Differential.bak'

		WITH DIFFERENTIAL,

		MEDIANAME = 'Native_SQLServerDiffBackup',

		NAME = 'Diff-AdventureWorks2019 backup';
}

How to create Differential database backup using SQL Server Management Studio (SSMS):

1. In the Object Explorer, right-click on the database you want to back up.

2. Navigate to Tasks > Back Up... This opens the "Back Up Database" wizard.

3. Select "Differential" as the backup type.

3. Transaction Log Backup

A log backup, as indicated by its name, serves to back up transaction logs. This backup method is applicable exclusively to full or bulk-logged recovery models within a database system. The transaction log file maintains a sequential record of logs that detail the history of each data modification in the database. A log backup encompasses all log entries that have not been covered in the most recent transaction log backup.

This approach permits the precise recovery of a database to a designated moment in time. In essence, transaction log backups operate on an incremental basis, while differential backups accumulate changes. For achieving a database restoration to a specific point in time, the procedure involves restoring a recent full backup, followed by a recent differential backup, and subsequently incorporating all pertinent transaction log entries that are necessary for reconstructing the database up to the intended temporal target. This process is underpinned by the Log Sequence Number (LSN) mechanism, which orchestrates the order of modifications within the log chain.

A log backup chain constitutes an uninterrupted sequence of logs encompassing all essential transaction log entries essential for database recovery to a chosen time point. The inception of a log chain invariably coincides with a full database backup, continuing unbroken until an event disrupts the chain, such as transitioning the database's recovery model to "simple," or conducting an additional full backup. This disruption temporarily halts the possibility of executing log backups for the database until another full (or differential) backup is initiated, thereby restoring the continuity of the log chain.

How to create Transactional Log backup using T-SQL:

{


		BACKUP LOG  [AdventureWorks2019]

		   To DISK=' D:\Backup\AdventureWorks2019_TC_Log.trn'

		   WITH

		   MEDIANAME = 'Native_SQLServerLogBackup',

		    NAME = 'Log- AdventureWorks2019_TC backup';
			}

How to create Transactional Log backup using SQL Server Management Studio (SSMS):

1. In the Object Explorer, right-click on the database you want to back up.

2. Navigate to Tasks > Back Up... This opens the "Back Up Database" wizard.

3. Select " Transactional Log" as the backup type.

4. Tail log backups

In the event of a failure, when you need the database to get back up and running, and the database is operating in FULL or BULK_LOGGED recovery model, it’s always easy to start the recovery operation and start restoring the backups. But before that, the first action to be taken after the failure is what is called as a tail log backup of the live transaction log.

How to create Tail Log backup using T-SQL:

{
	USE master;

	 GO

	 -- create a tail-log backup

	 BACKUP LOG [AdventureWorks2019]

	 TO DISK = 'D:\Backup\AdventureWorks2019_TCTailLog.log'

	 WITH CONTINUE_AFTER_ERROR;

	 GO
}

How to create Tail Log backup using SQL Server Management Studio (SSMS):

1. In the Object Explorer, right-click on the database you want to back up.

2. Navigate to Tasks > Back Up... This opens the "Back Up Database" wizard.

3. Select " Transactional Log" as the backup type.

4. Select “Backup the tail of the log, and leave the database in the restoring state” as the Media Options.

5. Copy_Only backup

A copy-only backup is a distinct variant of a full backup, breaking away from the regular backup sequence. Unlike a conventional full backup, a copy-only backup does not serve as the foundation for subsequent differential backups.

The scope of a full backup encompasses all database recovery models. In contrast, a copy-only backup is limited to full or bulk-logged recovery models. When it comes to restoring data from a copy-only backup, the process remains consistent with standard restoration procedures.

How to create Copy_Only backup database backup using T-SQL:

				{
						 

							BACKUP DATABASE [AdventureWorks2019]

							TO DISK = 'D:\Backup\ AdventureWorks2019_FullBackup.bak'

							WITH FORMAT, INIT;
				}

How to create Copy_Only backup database backup using SQL Server Management Studio (SSMS):

1. In the Object Explorer, right-click on the database you want to back up.