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.
- To recover lost data
- To minimize the data loss
- To minimize the downtime
SQL Server Recovery Models
Understanding the recovery models are essential to developing an effective backup strategy. A database recovery model determines how transactions are logged and the type of backups 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, whenever checkpoint runs in this model committed data moves to Mdf and the inactive portion of the 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 database crash is not possible. This model is used for development and test servers where data loss is acceptable.
Full Recovery Model
Full recovery provides the highest level of data protection by fully logging all transactions. It supports all types of backups. We can recover and restore data fully. It is capable of point-in-time recovery.
In the 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. Whenever transactions including BCP, BULK Insert, Create Index, Alter Index Rebuild, DBCC DBREINDEX, sql server identifies it is a bulk operation and starts logging data minimally.
Whenever we change the 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 the entire database including the active part of the 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 backups are incremental backups.
Differential backup uses a differential change map (DCM) page to record the changes on extent.
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 the value to 1 in the buffer. Differential backup captures all the modified pages by using these converted 1’s after the full backup.
In differential first time all modified pages will be backed up. In the next differential, it captures the latest modified pages along with the first differential modified pages. This process will continue till the next full backup, hence it is called incremental backup.
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 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.
- 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
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’
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.
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.
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
Below topics further included in this series
- SQL Server Database Administration Introduction
- SQL Server Architecture
- SQL Server Storage Architecture
- SQL Server Database Architecture
- SQL Server Transaction Log Architecture
- SQL Server installation
- SQL Server Databases
- SQL Server Backups
- SQL Server Restoration Tutorial
- SQL Server Security
- SQL Server Automation Tutorial
- SQL Server Migration
- SQL Server Upgradation
- SQL Server Log Shipping in SQL Server