Database Backup on Sql Server

Full Backup. This type of backup will lets you to backup all of extents of your database. In my previous articles, I've already explain about extent in SQL Server 2005. SQL Server always save your data in pages and extents. 1 pages contains 8 Kb of your data, and 1 extents is a group of 8 pages which contains 64 Kb data of your database. To use this type of backup, you must set your recovery model to full, before you backup your database. To set your database recovery model, you can use this query :

ALTER DATABASE [ database_name ]

SET RECOVERY [ FULL | BULK_LOGGED | SIMPLE ]

,and you can do full backup by using this query :

BACKUP DATABASE [ database_name ] TO DISK = '\' WITH INIT

TO DISK clause specify the location of your backup device, while WITH INIT clause is the common clause to tell SQL Server to overwrite existing data in backup device. You can make backup device by using SSMS or simply by using this query. Backup device is logical backup medium to save your backup data. It has extension .bak.

Differential Backup. This type of backup will backup your database since the last full backup. SQL Server will make extent map to recognize the new data in your database. When you insert data in your database, extent will have bit with 0 to 1 to represented their information. After you do full backup, it will be reseted to 0, in this way SQL Server know which data that have to be backed up in differential backup methods.Notes that you can do this type backup if you have done full backup to your database.

You can do differential backup by using this query :

BACKUP DATABASE [ database_name ] TO DISK = '\' WITH DIFFERENTIAL

Incremental Backup. This type of backup looks similar to differential backup. But actually, it's really different. Incremental backup will back up your database since the last full backup and the last incremental backup. So if you have 100 Mb data on Sunday, 150 Mb data on Tuesday, 200 Mb data on Wednesday, and 250 Mb data on Thursday. So the size of full backup data taken on Sunday is 100 Mb, while the incremental backup data taken on Tuesday, Wednesday, and Thursday are 50 Mb data for each day.

To restore this incremental backup, you must have all of your incremental backup data, or you won't be able to restore your database completely. For example, your incremental backup data on Wednesday is lost, you won't be able to restore your incremental backup data on Thursday. This type of backup are not recommended for production database, since it has a lot of risks.

Transaction Log Backup. This type of backup needs full backup of your database. Transaction Log Backup will back up all actives log files of your database. To use this type of backup you can use this query :

BACKUP LOG [ database_name ] TO DISK = '\' WITH INIT

You also can make operators and scheduled tasks to be assigned backup job to automated backup process of your database. I won't explain it this time, but I promise I'll tell you about this later.