Sql Server Backup Types

Backup is used to restore our database from any type of failure such failure can be of system crash or can be from SQL Server fails etc

. In sql there are mainly four types of backup each have some limitations and some features. Here are the names of different backups Full Backup, Differential Backup, Transaction Log Architecture Backup and last one is File and File-groups Backup. Now i will explain all of backup type in below paragraph.

First of all i will take Full database backup. It is the most common type of SQL Server backup.In full database backup we will take backup of whole database. Full Database Backups includes part of transaction log so we can recovered all the data from backup. Now we will talk about some good features of full backup. First one is that full backups results in a faster restore. It is very good where data is small. Now comes to some of its limitations We cannot use restore our backup in its earlier version for example we cannot store backup of sql2008 in sql2005. Next one is full database require lot of space as compare to other backup. Another drawback of full database backup is whenever we take a backup operation a entry is taken place in error log and in system event log. So this will increase the size of error logs.This allows for the simplest form of database restoration, since all of the contents are contained in one backup.

Second type of backup is Differential Backup as the name suggest here we will take a backups of files that have done changed after the last backup done. And list of files changed since the last back up is recorded in a catalogue file with .bkc extension. If our database is in simple recovery model we can use full and differential backups. If database is in Bulk Logged recovery model we can use differential backups to eliminate the number of transaction logs. Now comes to its advantages first one is it will take less space as compare to full backup. It will also reduce the time to take backup as we have to take less backup then the full backup. Another very good benefit if differential is the restoring of backup is faster because storage requirement is very less.

And third backup type is Transaction Log Architecture. As the name suggest transaction log all the traction and all the database modifications done by each transaction are stored and restore is done through this transaction logs. Now i will come to benefits of Transaction Log Architecture Backup. First one is transaction log backup allows us to backup the active part of the transaction log. So after you issue a Full or Differential backup the transaction log backup will have any transactions that were created after those other backups completed. Another good feature of this backup is that we can use the space which is used by transaction log to other process once we issued backup. We can recover individual transaction in case of failures. We can recover incomplete transaction with this backup. Now comes to some limitation of this backup is that when we not transaction log it will continue to grow more which take more space.

Now last which i have define here is File and File-groups Backup. We got almost idea from the name of backup. In this backup we will get backup done from individual files or from file-groups. We can use this types of backup where database is too big and we does not have time to take full backup. File and file groups can be restored from a full database backup set. Now comes to benefit of this backup it allows a quicker recovery because only the file and the file groups that are damaged are restored first.

by: ADAM

POST COMMENT

Backups - Why Backups Are A Good Thing Best Way to Recover Corrupt Backup File Not The Best Backup Sump Pump Take backup of my HDD on an external Drive? Considerations in Common Backup Processes Benefits of Remote Backup The Importance of a Battery Backup Importance of taking backups Restore Backup EXEC BKF File with a Fully Fledged Tool Quantum DAT-160 Tape The Industry-Leading Backup Solution How to Sing Backup Vocals Genie Backup Manager Pro 8.0 Breakthrough Features of Maxell LTO-5 Backup Tape