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
- To recover lost data
- To minimize the data loss
- To minimize the down time
SQL Server Recovery Models
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
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.
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
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
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.
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 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
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
Related SQL Server Tutorials