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.
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
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
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.
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';
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.
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.