An Abstract On SQL Server Backup

overview

SQL Server application is integrated with several High-Availability elucidations like Fail-over clustering, Database mirroring, or Always-on. These solutions make sure that the databases are available for maximum up time. However, in order to make sure that the databases are not lost or there is no data loss with any type of failure, users can create SQL Server backups. This segment will discuss about the backup and restore strategies and methodologies of the same.

Various Types of SQL Server Backup

• Full backups
• Differential backups
• Transaction log backups

Full Backups

This type of backup is the most common type of backup, and is known as database backup. It comprises the backup of databases along with part of the transactional log files. It provides simplest forms of database “backup and restore” method. This backup can be taken using T-SQL or SQL Server Management Studio.

Create SQL Server Backup using T-SQL:

BACKUP DATABASE KacyDB1 TO DISK = ‘C:\KacyDB1.BAK’
GO

SQL Server Management Studio:

• Right click on the database.
• Select Tasks > Backup.
  
• As Backup type, select “Full”.
• In the Destination, Select “Disk”.
• Click “Add…” in order to add backup file, type “Location\KacyDB1.BAK” and then click OK.
• Again, click “OK”.
  

Transaction Log Backup

Transaction Log backup can be issued when the database is set to either, “Full” or “Bulk-logged” recovery model. With the transaction log backup with full backup, user can perform the “point in time” restore operation easily. This is very important in the situation where the data has been accidentally deleted in order to restore the data before deletion occurred. This type of backup captures all the transaction log records, which are written after last transaction log backup. Transaction log backups are suggested in order to truncate the log to save the space not letting it grow too much.

Create Backup using T-SQL:

BACKUP LOG KacyDB1 TO DISK = ‘C:\KacyDB1.TRN’
GO

SQL Server Management Studio:

• Right click on the database.
• Select Tasks > Backup.
• Here, select “Transaction Log” as backup type.
• In destination, select disk.
• Click “Add…” in order to add backup file, type “Location\KacyDB1.BAK” and click OK.
• Again, click “OK”.
  

Differential Backups

Differential database backup comprises of the extents that were changed after the last full database recovery. Server checks the DCM (Differential Changed Map) in order to identify the extents that were changed after the last full backup. Since it focuses only on the changes made, this backup is quite fast as compared to any other backup type. An extent is generally made up of 8KB pages. Whenever any changes are made to the database, a flag is turned on and while creating Differential backup, data these extents are taken.

Create Backup using T-SQL:

BACKUP DATABASE KacyDB1 TO DISK = ‘C:\KacyDB1.DIF’ WITH DIFFERENTIAL
GO

SQL Server Management Studio:

• Right click on the database.
• Select Tasks > Backup.
• Here, select “Differential” as backup type.
• Select disk.
• Click “Add…” in order to add backup file, type “Location\KacyDB1.DIF” and click OK.
• Again, click “OK”.
  

Tail-Log Backup

In case your database is corrupted but the transaction log file, which is associated with the database is still available; Tail-log backup can be taken. This type of backup is extremely helpful when the databases are corrupted and any logs have not been backed up. It is possible only when the transaction log is not damaged. If it is in inconsistent state, this type of SQL Server backup is not possible.

File and FileGroup Backup

In case of large databases, there can be several files or file groups. In this case, you can perform the backup the frequently changing file or the filegroup. One can choose to backup and restore each individual file or whole filegroup instead of specifying particular component. This accelerates the speed of recovery by specifying the files which have been damaged instead of restoring all the files altogether.