Best Practices for Backup and Restore in SQL Server 2005

Backup Targets

Besides the backup type, SQL Server allows four different backup targets: Database, Filegroup, File and Partial. These targets only apply to the Full and Differential Backup types; they do not apply to the log backup.

Database Backup

A database backup makes a copy of the entire database. If you are using a Full database backup it includes all used database pages in the database; if you are using a Differential Database backup, it includes all changed database pages of the database that have changed since the last full backup. Some examples of how to perform a Full and Differential Database backup are:

-- Full Database Backup

BACKUP DATABASE AdventureWorks

  TO  DISK = N’C:\Backup\AdventureWorks.bak’ 

-- Differential Database Backup

BACKUP DATABASE AdventureWorks

  TO  DISK = N’C:\Backup\AdventureWorks.bak’

  WITH DIFFERENTIAL

Differential Backup

Differential backups are designed to reduce the amount of time it takes to perform full backups. Instead of copying all used pages, the differential backup copies only pages that have changed since the last full backup. Similar to the Full Backup, differential backups also copy a part of the transaction log to maintain the transactional integrity when the backup is restored. The following code is an example of how to create a Differential Backup. 

BACKUP DATABASE AdventureWorks

  TO DISK = N’C:\Backup\AdventureWorks.bak’

  WITH DIFFERENTIAL

Log Backup

The only type of backup that does not actually copy database pages is the Log backup. The Log backup copies the transaction log of the database. After it copies the log, the backup truncates the part of the log that is not required by active transactions or transactional replication. The syntax to back up the Log is:

BACKUP LOG AdventureWorks

TO  DISK = N’C:\Backup\AdventureWorks.bak’

File Backup

File backups do not copy the database; instead the file backup aim is to back up only one or more files that are part the database.Databases in SQL server 2005 have three different types of files: Primary, Secondary, or Log Files. Primary and Secondary files hold database pages; in contrast, Log files hold the transaction log. File backups can make copies of pages from Primary and Secondary Files.This type of backup can be used to speed up the restora-tion process, when the device that stores one of the files

fails but the rest of the devices are working correctly. In this case, there is no need to restore the full database; only the affected file.  The following SQL code performs Full and Differential File backups:

-- Full File Backup

BACKUP DATABASE AdventureWorks  FILE=’AdventureWorks_Data’

TO  DISK = N’C:\Backup\AdventureWorks.bak’ 

-- Differential File Backup

BACKUP DATABASE AdventureWorks  FILE=’AdventureWorks_Data’

TO  DISK = N’C:\Backup\AdventureWorks.bak’

  WITH DIFFERENTIAL

One important element to remember when using File Backups is that they should either be read only (the file-group they belong to) or combined with Log Backups to restore transactional integrity of the database. File backups do not automatically back up the transaction log.