SQL Server Backup Tutorial | SQL Server DBA Tutorial

SQL Server Backup Tutorial from Coding compiler | SQL Server DBA Tutorial – Part 3, this blog is the third tutorial in the series, here we are going to learn about these below topics. Before going to learn this tutorial, we recommend you to go through the SQL Server DBA Tutorial – Part 1 and SQL Server DBA Tutorial – Part 2.

Topics Covered in this SQL Server DBA Tutorial

Introduction to Backups
Recovery Models
Backup Types
Advanced Backup Types
Situations of Tail Log Backup

SQL Server BACKUPS

Backup means, saving the exact copy of the current version of database in some other location.

Situations where backup can use,

Hardware (or) software failure
Natural disasters
Internal misuse

Hardware failure includes operating systems CPU memory, network card failures. Software failure includes operating system failures database server failures. We have one more failure called storage failure. Natural disasters include natural calamities like cyclones, fire attacks. Internal misuse means someone in the organization wantedly (or) by mistake delete some data.

Backup Objectives:

To recover lost data
To minimize the data loss
To minimize the down time

SQL Server Recovery Models

Understanding the recovery models are essential to develop an effective backup strategy. A database recovery model determines how transactions are logged and type of back ups that can be performed on a database.

There are 3 recovery models,

Simple recovery model
Bulk logged recovery
Full recovery

Simple Recovery Model

Simple recovery supports full and differential backup “Truncate log on” checkpoint option will be enabled in simple recovery, when ever checkpoint runs in this model committed data moves to Mdf and inactive portion of virtual log file will be truncated, hence there will be no data in Ldf file for backup’s

Point – in-time restoration and point of failure when databas crash is not possible. This model is used for development and test servers where data loss is acceptable.

Full Recovery Model

Full recovery provides highest level of data protection by fully logging all transactions. It supports all type of backups. We can recover and restore data fully. It is capable of point-in-time recovery.

Bulk-Logged

When ever we perform bulk operations log file may full due to huge transactions. No new transactions will comes to mdf hence the transactions may fail.

In bulk logged recovery model it records minimal information about transactions. It logs the extent allocations and modified extents information only we cannot perform point-in-time recovery. When ever transactions including BCP, BULK Insert, Create Index, Alter Index Rebuild, DBCC DBREINDEX, sql server identifies it is a bulk operations and start logging data minimally.
When ever we change recovery model always take a full backup to avoid data loss. Steps to be followed

Change to bulk logged
Allow transactions to complete
Change to full and take full backup immediately.

SQL Server Backup Types

There are 3 basic types of backup’s

Full Backup
Differential Backup
Transaction Log Backup

SQL Server Full Backup

A full backup captures entire database including active part of transaction log. Any uncommitted transactions at the time of backup taking will be rolled back while restoring. For all backups full backup is the base. Sql server only backup the data types in the database excludes all empty data pages.

T-sql command to take full backup

Backup database databasename to disk = ‘E:\backups\test_full.bak’

SQL Server Differential Backup

It captures modified pages after the last full backup. Differential backup are incremental backups.

Differential backup uses differential change map (DCM) page to record the changes on extents.

All the extents are marked with O inside DCM page. For each modified page the extent will convert to 1. SQL server copies pages from mdf to buffer and converts value to 1 in buffer. Differential backup captures all the modified pages by using these converted 1’s after full backup.

In differential first time all modified pages will be backed up. In the next differential it captures latest modified pages along with first differential modified pages. This process will continue till next full backup, hence it is called incremental backup.

T-sql command:

Backup database sql test 2012 to disk = ‘E:\backup\test-differential.bak’ with differential

SQL Server T-Log Backup

T-Log backup copies the inactive portion of the log and truncates the inactive portion. Once it truncates log space can be reused by new transactions.

T-log backup are sequential backup’s. Every backup will have LSN (Log Sequential Number).

File Backups

File backups copies pages from primary and secondary files (or) file groups. This backup can be used to speed up restoration process. If we have multiple file group backup on separate disk. If single disk files we can restore that file backup of single disk instead entire database.

T-sql command for file back

Backup database sql test 20120 file = ‘sql 2012 test_data’ to disk=’E:\backups\sql test 2012_data.bak’;

For primary file group

Backup database sql test 20120 file group = ‘primary’ to disk = ‘E:\Backups\sql test 20120_primary.bak’;

SQL Server Partial Backup

Partial backups backup primary filegroup and every read and write filegroup that is part of database. Partial backup use read-write-file groups option in the backup command.

Backup database sql test 2012 read-write-filegroups to disk = ‘E:\Backup\sql test 20120.bak’

Taking backup using SSMS GUI,

Connect to sql server data through SSMS
Right click on database → from menu → select tasks → backup
Select the type of backup and provide destination path, general tab provide below details.

Select the database from dropdown, recovery model always be full, select database option in backup component. Backup set group all files in single location specify retention period of back in days (or) provide exact date.

Using add button provide backup filename for the backup device. If we want we can remove.

Backup to existing media → Backup media will not overwritten

Append → Add backup files to end of existing files.

Overwrite → Overwrite existing backup files.

Verify back → verifies whether data successfully copied or not.

Checksum → checksum option verifies backup process will that the page checksums that exist on data file. If a bad page checksum is found, backup process will stop.

Continue on error → we are asking sql server to continue even though there is error in backup process.

Advanced Types of Backups in SQL Server

Copy only backup
Compressed backup
Split backup
Tail log backup

1. SQL Server Copy Only Backup

Copy only backup are used to perform a full (or) T-log backup without breaking the log (LSN) chain and without distributing regular backups schedules. Whenever we get ad hoc request for backups we can use this copy backup.

Through GUI

Select copy only backup option, in backup window.

Through T-sql command

Backup database databasename = ‘E:\backups\sqltest2012-copyonlybak’ with copy only

From sql server 2008 we can able to take copy-only backup using GUI. In sql server 2003 we can only take using T-sql command

For log

Backup log sqltest2012 to disk =’E:\backups\sqllog.trn’ with copy only

2. SQL Server Compressed Backup

From sql server 2008 this introduced, when ever we take backup it will compress the backup, it will not touch original database. It saves disk space and time. Once we restore it uncompress the backup to original size.

We can use sp-configure stored procedure with ‘backup compression default’ 1 to enable and 0 to disable default compression for all backups in the server.

Sp-configure ‘backup compression default; ‘1’  Reconfigure with override.

In the backup window select compression option at server then compression will be enabled at server. Next time onwards for all backup it compress the backup output file.

Set backup compression

Using T-sql command

Backup database sqltest2012 to disk=’E:\backups\sqltest.bak’ with compression=1

‘1’ is to turn on compression, ‘0’ is to Turnoff.

3. SQL Server Split Backup

Whenever we have less space in drives for large data we can break (or) split the backup into other drives which we gave space. It splits backup output in multiple files (drives).

Performance will be very faster because more I/O of each drive will work hence backups will be faster.

We have one disadvantage while restoring if any split misses we cannot restore entire database.

Backup database databasename to disk = ‘E:\backups\sqltest1.bak’

to disk = ‘F:\backups\sqltest2.bak’

to disk = ‘G:\backups\sqltest3.bak’

Through GUI

In backup window we specify more paths by adding E,F,G drives for splitting.

4. SQL Server Tail Log Backup

Tail log backup refers the content of inactive portion of transaction log that has not been backed up. It is just like normal T-log backup. This backup is always not possible to take.

Situations of Tail Log Backup

1. Whenever database is corrupted (or) damaged.

If log file drive is still available, we keep database in emergency state so that database will start with available files and drives. Emergency state makes database read only and restrict the access to system admin only. Take tail log backup

To keep database in emergency state
Alter database sql test 2014 set emergency
Perform tail log back immediately using NO – TRUNCATE

Command to take:

Backup log sql test 2014 to disk = ‘E:\backups\sqltest-tail.trn’ with NO - TRUNCATE

2. We can use No-recovery option

No recovery means no one access the database until restoration complete. Here we are preventing user to access the database until restoration completes.

Backup log database name to disk: ‘path’ with No-recovery

In No-Truncate we are saying not to truncate the inactive portion until we complete the log backup in crash situation.

SQL Server Backup Options

Init / No init

Init stands for initialization, when we take backup init write a record history of backup in MSDB database but it overwrites the existing history.
Noinit writes one new record under existing record.

Skip / No skip

When ever we perform backup we can verify whether it properly write a copy (or) not.

Skip will skip verification process
Noskip will verify the verification process.

Stats

When ever we take backup for seeing status of that backup percentage completed. By using this option we can estimate how much time will backup takes.

Backup database databasename to disk = ‘path’ with stats=10

SQL Server Backup History

We can see what are backups available in server from backup history table of MSDB database.

Select * from msdb.db0.backupset

SQL Server Backup Permissions

For taking backup we need certain permissions

System admin (sa)
DB owner
DB creator
DB backup operator

We need any of the 4 roles for taking backup.

Backup History

We can get details of the database who has taken backup, what type of backup it is. When this backup start and end, LSN’s all these details we get by querying msdb tables using query.

 Select * from msdb.db0.backupset

D – Database backup

I – Incremental/differential backup

L – Log backup

SQL Server DBA Tutorial – Part 3