Using Full Database Backups

A very common backup strategy is to back up the whole database in a predefined time series (once each night, for instance). With such a backup strategy, it is possible to recover a database to the state it had when the last backup occurred. This strategy is implemented by using full database backups, which are explained below.

A full database backup contains all data and database meta information needed to restore the whole database, including full-text catalogs. When you restore a full database backup, it restores all database files yielding data in a consistent state from the time the backup completed. While the backup is performed, the database is online and users can send transactions, changing data as usual. The term consistent state means that all transactions that are committed while the backup is being performed are applied and all transactions that are not finished are rolled back. To handle situations that would lead to inconsistencies due to transactions changing data while SQL Server is performing backups, SQL Server has a special process to guarantee data consistency. This process involves writing both data pages and transaction log records to the backup device.

Tip

Full-text catalogs are added to databases to enable SQL Server's full-text indexing functionality. Full-text indexing allows you to perform quicker, more accurate searches on data in your database. For more information on full-text indexing, see the SQL Server Books Online topic "About Full-Text Indexes."

The speed of the backup is determined by the underlying I/O devices (Input/Output devices, used to gather and store data). To get the best performance, SQL Server reads the file sequentially. If your I/O devices are capable of handling the I/O resulting from backing up data along with the I/O produced by normal system use, then creating a backup has only a minimal effect on the performance of your system. Nevertheless, it is a good practice to perform full database backups in off-peak hours.

In the following section, we will discuss options for implementing such a backup strategy.

The Simple Recovery Model

SQL Server needs to know which kind of backups you plan to perform on a database in advance; you need to configure the database in the manner required for performing the types of backups you will be using. This configuration is done by setting the recovery model database option. The default recovery model a database uses is derived from the recovery model of the model database specified at its creation. To implement a backup strategy that includes only full database backups, the recovery model should be set to SIMPLE.

Setting the Recovery Model to SIMPLE

1.	From the Start Menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.
2.	In the Connect to Server dialog box, click the Connect button.
3.	From the Standard toolbar, click the New Query button to open a New Query window.
4.	To set the recovery model, you can use an ALTER DATABASE statement. Type the following statement and click the Execute button. USE master; GO ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE; GO

More Info

This chapter focuses on accomplishing backups and restores using T-SQL statements. Chapter 4, "Transferring Your Database to Other Systems," will discuss how to perform many of these same procedures using the SQL Server Management Studio user interface instead of T-SQL statements.

Verifying the Recovery Model Setting

1.	To view and verify the recovery model of a database, you can use the DATABASEPROPERTYEX function, which retrieves the current database options or properties of the specified database. Execute the statement below to retrieve the recovery model of the AdventureWorks database. SELECT DATABASEPROPERTYEX('AdventureWorks','Recovery')
2.	Verify that the result of the query shows SIMPLE as the recovery model.
3.	Close SQL Server Management Studio.

Backup Devices

Before starting a backup, you need to know where it will be stored. The storage location of a backup is called a backup device. Each backup device can store multiple backups of different types. There are two different types of backup devices.

• Tape devices Can be used to store backup data on tapes. Tape devices must be attached locally. Backups can span several tapes, and SQL Server backups can be mixed with Windows backups.

• Disk devices Files on the local or remote disk or disk storage media. They are referenced using the path to the file where the backups are stored. Remote locations must be referenced using the UNC Path.

Note

We will discuss only backups to disk devices in this book. SQL Server backups to tape devices are no longer very common. When SQL Server backups are stored on tapes, they are normally performed using a third-party vendor product that offers additional functionality, like remote tape storage. Alternatively, a tape device might be used as additional insurance to backup data that has already been backed up to a disk device.

Backup devices are identified by a device name. A device name can be a logical or a physical device name. The physical name of a disk device is the path of the backup file, such as '\\BACKUPSERVER\Backups\adv\AdventureWorks.bak'. This path can be used directly in the backup statements. Logical device names are names stored in SQL Server that point to the physical names of the backup devices. When a logical device name is used in the backup statement, SQL Server searches for the corresponding physical location in its system catalog and performs the backup to this location.

To add a logical device to the system catalog, you can use the sp_addumpdevice system stored procedure. The example below defines a logical device called Adv_FullDb_Dev.

EXEC sp_addumpdevice 'disk', 'Adv_FullDb_Dev', 'T:\BACKUPS\AdvFullDbDev.bak';

Tip

Be sure to change the file path to one that is appropriate to your machine. If you don't have a drive mapped to T:\, change this part of the above file path to match the drive mapping on your machine. Also, be sure any folders specified in this file path exist on your machine.

Logical and physical device names can be used interchangeably when backing up and restoring a database. Of course, it is generally a good idea to consistently use one of the two naming conventions so as not to complicate your code. You should decide in advance which naming convention you prefer.

Backups should never be done to a disk device that resides on the same physical storage unit as the database itself. Even when the disk storage has a failure tolerance through some RAID level, it is always possible that the controller might fail and destroy the data on the disks. Also, you should consider archiving the backup device files to tape and storing the tapes at a remote location.

Tip

The acronym RAID stands for "redundant array of independent disks." These arrays are disk systems with multiple drives used to improve reliability and storage capacity.

Performing Full Database Backups

After setting the recovery model to SIMPLE and deciding on which backup devices you want to store your backups, you can start performing backups. Full database backups are performed using the BACKUP DATABASE statement, which is quite easy to use. In the simplest form, you only need to tell the system which database to back up to which device. To back up the database AdventureWorks to the logical device you defined earlier, the statement is as follows:

USE master; GO BACKUP DATABASE AdventureWorks TO Adv_FullDb_Dev;

If you want to perform a full database backup to a physical device, you have to specify the device type and the location in the BACKUP DATABASE statement. To back up the database to the location t:\adv.bak, use the statement below.

USE master; GO BACKUP DATABASE AdventureWorks TO DISK='t:\adv.bak';

As mentioned before, every backup device can store more than one backup. You can specify whether you want SQL Server to overwrite or append to existing backups on the device in an argument of the BACKUP DATABASE statement. The options used for this are INIT and NOINIT. If you specify INIT, the backup device is truncated before the backup starts, overwriting any backup existing on the device. NOINIT, which is the default if you specify nothing, lets SQL Server append the backup to the existing backup device, preserving all existing backups. Options are set through a WITH block at the end of the BACKUP DATABASE statement.

If you want to do the same backup as the previous example but tell SQL Server to truncate the device first, use the following statement:

USE master; GO BACKUP DATABASE AdventureWorks TO DISK='t:\adv.bak' WITH INIT;

As you can see, performing full database backups is quite simple. You will see in the next section that full backups are the backup type that all other backup types rely on. Other backup types depend on full database backups because they need a rebuilt database from which to work. These other types of backups, including differential backups, store changes that have occured in the database since the base full backup was taken. Thus, you will see that full database backups are not only important in a recovery strategy where only full database backups are performed, but also in the backup strategies we will discuss next.