Backup Types in SQL Server 2008
Full DB Backups
A full DB backup is an all-inclusive backup that captures an entire DB in one operation. This full backup can be used to restore a DB to the state it was in when the DB backup completed. The backup is transactionally consistent, contains the entire DB structure, and contains the related data stored in these structures. As with many other backups, SQL Server allows for updates to the DB while a full backup is running. It keeps track of the changes occurring during the backup by capturing a portion of the transaction log in the DB backup. The backup also records the log sequence number (LSN) when the DB backup is started, as well as the LSN when the DB backup completes. The LSN is a unique sequential number you can use to determine the order in which updates occur in the DB. The LSNs recorded in the backup are used in the restore process to recover the DB to a point in time that has transactional reliability.
A full DB backup is often used in conjunction with other backup types; it establishes a base for these other types if a restore operation is needed. The other backup types are discussed in the subsequent sections, but it is vital not to forget about the full backup that need to be restored first in order to utilize other backup types. As an example, let's say you are making hourly transaction log backups. If the DB is to be recovered using those transaction log backups, the last full DB backup need to be restored first, and then the subsequent log backups can be applied.
Running a Full DB Backup
A full DB backup, without the use of other DB backups, is often found in nonproduction environments where the loss of transactional data is relatively unimportant. Some development environments are good examples of this. In these environments, a nightly full backup is sufficient to confirm that recent Data Definition Language (DDL) changes and the related development data for the day are captured. If a catastrophic failure occurs during the day and causes a restore to occur, the DB can be restored from the prior night's backup. The subsequent example displays a full backup of the AdventureWorks2008 DB:
--Full DB Backup to a single disk device
BACKUP DB [AdventureWorks2008]
TO DISK = N1
D:\mssql2008\backup\AdventureWorks2008.bak1
WITH NOFORMAT, INIT, NAME = N1
AdventureWorks2008- Full DB Backup1
,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
The sole use of daily full DB backups needs to be carefully considered. The benefits of limited administration and limited backup space requirements have to be weighed against the costs of losing an entire day's transactions.