RSS

An Abstract On SQL Server Backup

14 Sep

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.
    sql-server-backup-overview
  • 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”.
    backup-database

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”.
    backup-database-kaycy

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”.
    remote-desktop-connection

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.

 

 
1 Comment

Posted by on September 14, 2015 in backups

 

Tags:

One response to “An Abstract On SQL Server Backup

  1. Mustafa EL-Masry

    September 14, 2015 at 11:20 PM

    Good post Andrew

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s