Backups techniques and script

Back Up Your Database in SQL Server 2005

There are several types of backup methods in SQL Server 2005. In this time, I'll tell you about full backup, differential backup, incremental backup, and transaction log backup.
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

I have picked up this article from http://netindonesia.net/blogs/ziglaret/archive/2008/05/20/backing-up-database-in-sql-server-2005.aspx