Backup means, saving the exact copy of the current version of database in some other location.
Situations where backup can use,
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.
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 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 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.
Whenever we perform bulk operations log file may be full due to huge transactions. No new transactions will come to mdf hence the transactions may fail.
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
There are 3 basic types of backup’s
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
1 |
Backup database databasename to disk = ‘E:\backups\test_full.bak’ |
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.
1 |
Backup database sql test 2012 to disk = ‘E:\backup\test-differential.bak’ with differential |
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.
1 |
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
1 |
Backup database sql test 20120 file = ‘sql 2012 test_data’ to disk=’E:\backups\sql test 2012_data.bak’; |
For primary file group
1 |
Backup database sql test 20120 file group = ‘primary’ to disk = ‘E:\Backups\sql test 20120_primary.bak’; |
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.
1 |
Backup database sql test 2012 read-write-filegroups to disk = ‘E:\Backup\sql test 20120.bak’ |
Taking backup using SSMS GUI,
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.
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
Through T-sql command
1 |
<strong>Backup database databasename = ‘E:\backups\sqltest2012-copyonlybak’ with copy only</strong> |
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
1 |
Backup log sqltest2012 to disk =’E:\backups\sqllog.trn’ with copy only |
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.
1 |
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
1 |
Backup database sqltest2012 to disk=’E:\backups\sqltest.bak’ with compression=1 |
‘1’ is to turn on compression, ‘0’ is to Turnoff.
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.
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.
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
Command to take:
1 |
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.
1 |
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.
When ever we perform backup we can verify whether it properly write a copy (or) not.
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
We can see what are backups available in server from backup history table of MSDB database.
1 |
Select * from msdb.db0.backupset |
For taking backup we need certain permissions
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.
1 |
Select * from msdb.db0.backupset |
D – Database backup
I – Incremental/differential backup
L – Log backup
[…] SQL Server BACKUPS […]
[…] SQL Server BACKUPS […]