SQL Server 2005 Backups

The Types
There are 7 different types of backup available in SQL Server 2005:
1) Full (which is not the same as the Full Recovery Model)
2) Transaction Log
3) Tail-log
4) Differential
5) File / Filegroup
6) Partial
7) Copy-only

Full
This backs up all data, any changes during the backup process and any uncommitted transactions in the log (why uncommitted? because you might do a log backup after doing the Full backup). The init option overwrites the existing backup whereas noinit appends. The format option allows initializing of a tape if you’re using it for media. Here’s an example:
backup database [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_sched.bak'
with noformat, init, name = N'my_db-Full Database Backup', skip,
norewind, nounload, stats = 10
go

Transaction Log
This backs up the log from the last successful ‘backup log’ to the end of the current transaction log. It also truncates or discards the log information up to the beginning of the active portion of the log (as with truncate is the default). Here’s an example:
backup log [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_log_sched.bak'
go

Tail-log
Only done in emergencies when the data files are inaccessible but the log file is not damaged. Here’s an example:
backup log [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_tail-log.bak'
with no_truncate
go

Differential
Backs up the extents in the database that have changed since the last full database backup including changes during the backup process and any uncommitted transactions in the log. Here’s an example which appends the differential to the previous full backup:
backup database [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_sched.bak'
with differential, name = N'my_db-differential Database Backup'
go

File / Filegroup
For very large databases that distributed into various files and filegroups, it is possible to backup specific files and filegroups. See more detailed information here.

Partial
Allows full or differential backups of only part of the db. You may, for example, have two filegroups in your db and one is read-only. You only need one backup of the read-only and then you can do partial backups on the rest. See the above link for more details.

Copy-only
This is only available through T-SQL and is commonly used in Development situations. It doesn’t effect normal backup and restore files. For example, if you regularly do a full backup at 11pm and then differentials every four hours, doing a full backup will effect the backup set since the next differential will now rely on the new full backup. Using copy-only avoids this limitation. A great place to use this would be for production db’s to test on development servers. Here’s an example:
backup database [my_db]
to disk = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\my_db_copy_only.bak'
with init, copy_only, name = N'my_db-Full Copy_Only Backup'
go

Backup Integrity
There are three ways to ensure your backups aren’t bad or compromised. Backup using the ‘checksum’ with option which would be verified during the restore (the time added is not noticeable on most machines). Also you could test your backup immediately after doing it using the ‘restore verifyonly … with checksum’ command. This verification is quickly and easily done and should be integrated into any current backup strategy. The last method is ‘Backup Mirroring’ and is only available with the Enterprise edition and only if you have identical hardware to do both backups on.

Other Links:

Advertisements

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , , . Bookmark the permalink.

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