SQL Server Backup Types

Backup means exact copy of database.

Why we need backups?

1. Hardware or Software failure:
Hardware failure includes operating systems CPU, memory, network card failures.
Software failure includes Operating System failures, database server failures.

2. Natural Disasters:
Natural disaster include like natural calamities like Cyclones, fire attacks, etc.

3. Internal Misuse:
Internal Misuse means some one in the organization wanted or by mistake delete some(or full) data.

Objectives of Backups:
To recover lost data
To minimize the data loss
To minimize the downtime.

SQL Server Backup Types:

There are three types of backups

1. Full Backup
2. Differential Backup
3. Transaction Log Backup

Full Backup
A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
This includes part of the transaction log so that the full database can be recovered after a full database backup is restored. Full database backups represent the database at the time the backup finished.

T-SQL command to take full backup:

backup database k2tsql to disk=’C:\Program Files\Microsoft SQL Server\MSSQL12.PRD1\MSSQL\Backup\k2tsql.bak’

Processed 296 pages for database ‘k2tsql’, file ‘k2tsql’ on file 1.
Processed 2 pages for database ‘k2tsql’, file ‘k2tsql_log’ on file 1.
BACKUP DATABASE successfully processed 298 pages in 1.131 seconds (2.052 MB/sec).

Differential Backup

Differential backup captures modified pages after full backup. Differential backups are incremental backups. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup.

backup database k2tsql to disk=’C:\Program Files\Microsoft SQL Server\MSSQL12.PRD1\MSSQL\Backup\k2tsql_diffential.bak’ with differential

Processed 40 pages for database ‘k2tsql’, file ‘k2tsql’ on file 1.
Processed 1 pages for database ‘k2tsql’, file ‘k2tsql_log’ on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.300 seconds (1.067 MB/sec).

Transaction Log Backup
Transaction Log Backup copies the inactive portion of the log and truncates the inactive portion. Once it truncates log space can be reused ny new transactions. Transaction Log Backups are sequential backups. Every backup will have Log Sequence NUmber(LSN).

We must have created at least one full backup before we can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up.

backup log k2tsql to disk=’C:\Program Files\Microsoft SQL Server\MSSQL12.PRD1\MSSQL\Backup\k2tsql_log.bak’

Processed 3 pages for database ‘k2tsql’, file ‘k2tsql_log’ on file 1.
BACKUP LOG successfully processed 3 pages in 0.117 seconds (0.179 MB/sec).

File Backup

Powered by k2schools