SQL Server Database backups

Various notes and comments about SQL Server backups.

SQL Server Recovery Models:

Full

Bulk-logged (allows you perform all backups of full recovery model but bulk operations are not logged)

Simple (allows you to perform full backups and differential backups but NOT transaction log backups)

More About the Bulk-Logged Recovery Model:

Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage. However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.

Different Types of Database Backups:

Full backups.

Differential backups.

Transaction log backups.

NOTE: transaction log backups are smaller and faster than differential backups so if you need to backup your data every 30 minutes use the full recovery module and transaction log backups rather than doing a differential backup every 30 minutes.

Snapshots:

SQL Server database snapshots can be used for other purposes besides serving as a backup. But snapshots can also function as a "backup". See here for more information: SQL Server Database Snapshots

Backup Compression in SQL Server 2008 Enterprise:

You can turn on backup compression as the default configuration option using the sp_configure system stored procedure. SQL Server 2008 R2 Standard also supports backup compression.

Special kind of backups:

* Copy only backups.

* If you have multiple filegroups, you can backup just a single filegoup.

* If you have multiple files that make up a filegroup, you can backup just a single file in that filegroup.

* If you have multiple filegroups, you can set some to be "read-only". You can then backup the "read-write" filegroups only:

BACKUP DATABASE Databasename READ_WRITE_FILEGROUPS to MyBackupDevice

Making a backup with T-SQL:

You want to create a backup that expires in 10 days, and is appended to existing backup sets:

BACKUP DATABASE sales

TO DISK = 'C:\sales1a.bak'

WITH

BLOCKSIZE = 65536,

RETAINDAYS = 10,

NAME = 'FULL BACKUP OF SALES'