Types of Backups

From SQLServerPedia

Jump to: navigation, search
See Also: Main_Page - Database Administration - Backup & Recovery
SQL Server 2000 supports three main types of backups: Full, differential and transaction log backups. The Enterprise Edition of SQL Server 2000 also supports snapshot backups, however this type of backup requires the purchase of additional third party software and hardware.

A full backup makes a full copy of the database. This type of backup is most reliable, but it is also the most time and resource consuming. Even so, users can continue connecting to the database, reading and changing data while full backup is in progress. Full backups take longer than any other type of backup, but they're also easiest to recover from as long as no transactional activity has occurred since the last full backup.

A differential backup records changes that have occurred in the database since the last full backup. Differential backups are faster and smaller than full backups. However, in case of a disaster the full backup must be restored prior to restoring any differential backups. Suppose you take a full backup every 12 hours (12AM and 12 PM) and a differential backup every 4 hours. Further suppose that the database gets corrupted due to a hardware failure at 4:15PM. You must restore the full backup from 12PM first, followed by the differential backup from 4PM. In this scenario, if you weren't taking transaction log backups, you would only lose transactions that occurred from 4PM to the time when database became corrupted.

Transaction Log backups let you backup transactions that have occurred since the last full or differential backup, or since the last time a transaction log backup was taken. Transaction log backups are not supported by the SIMPLE recovery model (recall that the 'trunc. log on chkpt.' option has the same effect as the SIMPLE recovery model). Transaction log backups can only be restored after a full backup and differential backups (if any) have been restored. Transaction log backups impose less overhead on the server than full and differential backups; therefore, transaction log backups should be taken more frequently. Although transaction log backups take the least amount of time to generate, the total time required for recovering a database using such backups is the longest; you must restore the full backup first, then differential backups (if any) followed by all transaction log backups taken since the last differential backup.

Transaction log backups must be restored in sequence. For instance, suppose you have taken a full backup at 12AM and transaction log backups every 30 minutes since then. If a disaster strikes at 2:35 AM you must:
  1. Restore the full backup from 12 AM
  2. Restore transaction the log backup from 12:30 AM
  3. Restore transaction the log backup from 1:00 AM
  4. Restore transaction the log backup from 1:30 AM
  5. Restore transaction the log backup from 2:00 AM
  6. Restore transaction the log backup from 2:35 AM


In this scenario you would only lose transactions that occurred between 2:30 and 2:35AM. Now suppose we had taken a full backup at 12AM, transaction log backups every 30 minutes and a differential backup at 2AM. To recover from a disaster at 2:35 AM we would have to:
  1. Restore the full backup from 12 AM
  2. Restore the differential backup from 2:00 AM
  3. Restore the transaction log from 2:30 AM


If you attempt to restore transaction logs in any sequence other than in the sequence they were backed up, SQL Server will return an error similar to the following: 
The log in this backup set terminates at LSN %.*ls, which is too early to apply to the   database. A more recent log backup that includes LSN %.*ls can be restored.


To minimize the recovery time it is recommended to use a combination of full, differential and transaction log backups. In addition, you can perform full and differential backups of <a href="BAR2#B3">individual files and filegroups]] to further reduce the time required for a full recovery.

Snapshot backups can be useful in an environment with a very large database (VLDB) where downtime must be absolutely minimal. Databases are typically considered VLDB if their size is larger than a Terabyte. With snapshot backups the third party software creates an instantaneous copy of the data that is being backed up, which is accomplished by splitting a mirrored set of disks. This results in large backups that can be taken within seconds. Snapshot backups can also be restored very quickly. Such advantages come at an additional cost of third party hardware and software, however.

In addition to performing SQL Server backups, you should also consider backing up individual database files (.MDF and .LDF files), as well as backing up files to tape using Windows 2000 backup or third party software. In some cases you might be able to restore a database from .MDF files.

Information about backups and restores are stored in MSDB system database.

Contents

Full Database Backup with Transact-SQL





Option Explanation
Database_name | @database_name Database to be backed up
<backup_device> Backup device to which database backup will be written. You can specify multiple devices. This can be a logical or physical device
logical_backup_device_name | @logical_backup_device_name_var Logical device name created by sp_addumpdevice system procedure.
{ DISK | TAPE } =
'physical_backup_device_name' |
@physical_backup_device_name_var		 Full path to the location where backup should be written, if backing up to disk. Tape volume, if backing up to tape.
BLOCKSIZE = blocksize |
@blocksize_variable		 Specifies physical block size to be used for backup. Default is 64Kb, which also happens to be the upper limit for SQL Server. If backing up to DISK this parameter is not required- SQL Server will choose the most appropriate block size. If backing up to TAPE then check vendor documentation for appropriate block size.
DESCRIPTION = 'text' | @text_variable Free form text for identifying the backup device
DIFFERENTIAL Specifies that only the differential backup must be performed. Differential backup copies the portion of the database that has changed since the last full backup
EXPIREDATE = { date | @date_var } |
RETAINDAYS = { days | @days_var }		 EXPIREDATE specifies when the current backup set expires and can be overwritten. RETAINDAYS is the number of days from the time of backup before media can be overwritten
PASSWORD = { password | @password_variable } Supplies password for the backup set. Password protects the backup from being restored by unauthorized users, however, does not protect the backup set from being overwritten
FORMAT | NOFORMAT FORMAT advises SQL Server to write media headers to all volumes used for backup. Existing headers are overwritten. NOFORMAT indicates that media headers should not be written
INIT | NOINIT INIT overwrites the existing backup sets, but does not overwrite the media header. Backups are not overwritten however if they haven't expired. Backups are also not overwritten if the name specified in BACKUP statement does not match the name on the backup media. NOINIT appends backup to the media without overwriting. If not specified NOINIT is the default
MEDIADESCRIPTION = { 'text' | @text_variable } Free form text for identifying media set
MEDIANAME = { media_name | @media_name_variable } Media name for the entire backup media set.
MEDIAPASSWORD = { mediapassword | @mediapassword_variable } Creates a password for media set. If there is a password on media set the password must be specified to create a backup on this set
NAME = { backup_set_name | @backup_set_name_var } Name of the backup set
NOSKIP | SKIP NOSKIP checks the expiration date of the backup set, as well as name prior to overwriting it. SKIP does not check name and expiration date
NOREWIND | REWIND NOREWIND prevents all applications from using the tape until SQL Server issues BACKUP or RESTORE command with REWIND. Therefore SQL Server retains the control of the tape. REWIND advises SQL Server to rewind the tape and release control of the tape These options are only used for tape devices
NOUNLOAD | UNLOAD NOUNLOAD specifies that tape is not automatically unloaded from the tape drive. NOUNLOAD allows the tape to be reused for subsequent BACKUP / RESTORE operations. UNLOAD specifies that the tape will be automatically rewound and unloaded when BACKUP operation is complete These options are only used for tape devices
RESTART Restarts a backup operation that has previously failed
STATS [ = percentage] Displays a progress message when the specified percentage of backup is complete. Default is 10%


Default extension of database backups is .BAK. Unless you have a solid business reason for changing the extension, you should leave it as the default.

As you can see, the BACKUP DATABASE statement has numerous options, many of which are optional. Typical backups only include a few of the options discussed above. For example, the following statement performs a full backup of pubs database to a local disk drive: 
BACKUP DATABASE pubs  TO DISK = 'e:\pubs.bak'


Output looks similar to the following: 
Processed 240 pages for database 'pubs', file 'pubs' on file 1.  Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.  BACKUP DATABASE successfully processed 241 pages in 0.482 seconds (4.084 MB/sec).


Notice that we can also backup database to a network share, as shown below: 
BACKUP DATABASE pubs   TO DISK = '\\remote_server\share_name\pubs.bak'  WITH INIT, STATS = 20


SQL Server Agent must have read / write permissions to the backup destination share. Typically local backups are faster than network backups.

The next example overwrites the existing backup of pubs and specifies the percentage for returning progress message: 
BACKUP DATABASE pubs  TO DISK = 'e:\pubs.bak'  WITH INIT, STATS = 40


Results: 
49 percent backed up.  99 percent backed up.  Processed 240 pages for database 'pubs', file 'pubs' on file 1.  Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.  BACKUP DATABASE successfully processed 241 pages in 0.403 seconds (4.881 MB/sec).


Alternatively we could backup the database to multiple files, without overwriting existing backups as follows: 
BACKUP DATABASE pubs  TO DISK = 'e:\pubs1.bak',     DISK = 'c:\pubs2.bak'  WITH NOINIT, STATS = 10


SQL Server 2000 does not require creating backup devices prior to backing up databases. However, the ability to create a backup (dump) device is still supported. The following example creates a backup device and then backs up pubs database to that device: 
sp_addumpdevice 'disk', 'pubs_backup', 'e:\pubs_backup.bak'  GO  BACKUP DATABASE pubs  TO pubs_backup  WITH NOINIT, STATS = 10


Click here for information on restoring full backups.

Differential Backups with Transact-SQL



Differential backups have the same syntax as full backups, bar the DIFFERENTIAL keyword. Differential backup can be performed only after a full backup of the database has been taken. The following example creates a differential backup of pubs database: 
BACKUP DATABASE pubs  TO DISK = 'e:\pubs_differential'  WITH DIFFERENTIAL, STATS = 10


Output looks similar to the following: 
99 percent backed up.  Processed 16 pages for database 'pubs', file 'pubs' on file 1.  100 percent backed up.  Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.  BACKUP DATABASE WITH DIFFERENTIAL successfully processed 17 pages in 0.226 seconds (0.584 MB/sec).


Click here for information on restoring differential backups.

File and File Group Backups



SQL Server allows you to backup individual files and file groups, as opposed to backing up the entire database. Although users can continue reading and writing data while backup is generated the performance could worsen because of disk I/O overhead. This is particularly true if you store data files and backup files on the same disk. You should avoid such practice unless absolutely necessary. Since file and file group backups could be considerably shorter than a full backup of the whole database, such backups minimize the performance impact.



The following statement backs up the primary file group of the northwind database:

BACKUP DATABASE [northwind]  FILEGROUP = N'PRIMARY' TO  DISK = N'c:\nwnd_primary_backup.bak'  WITH NOFORMAT, NOINIT,  NAME = N'northwind-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10


The next statement backs up only a single file from the same database: 

BACKUP DATABASE [northwind]  FILE = N'Northwind_data2' TO  DISK = N'c:\nwnd_data2_backup.bak'  WITH NOFORMAT, NOINIT,  NAME = N'northwind-Full File Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10


Click here for information on restoring file / filegroup backups.

Backing up the Transaction Log with Transact-SQL

The default extension of log backups is .TRN. Unless you have a solid business reason for changing the extension, you should leave it as the default.

The syntax for backing up the transaction log is very similar to the syntax of creating full and differential backups. Please refer to the table above for a full description of each option. BACKUP LOG syntax provides three alternatives for managing the inactive portion of the log.

The NO_TRUNCATE option allows backing up of the transaction log when the database is damaged. This option does not truncate the inactive (already committed) portion of the log. Executing BACKUP LOG with NO_TRUNCATE on a regular basis will keep expanding the transaction log and you may run out of disk space quickly.

The NO_LOG and TRUNCATE_ONLY options act the same way - they truncate the inactive portion of the log without backing it up. These options are useful if you run out of disk space. If this happens, no transactions can be committed until you free up some disk space. Truncating the log allows you to clear the inactive portion of the log. You can execute DBCC SHRINKFILE to shrink the log files after truncating them. Recall that only Bulk-Logged and FULL recovery models allow transaction log backups. Note however, that the SIMPLE mode supports BACKUP LOG … WITH TRUNCATE_ONLY.

The following example backs up the pubs database's transaction log to a local disk: 
BACKUP LOG pubs   TO DISK = 'e:\pubs_log.trn'  WITH NO_TRUNCATE


When using TRUNCATE_ONLY or NO_LOG you do not have to specify the backup destination because the backup is not taken. SQL Server simply clears the inactive portion of the log. The following example truncates the log of the pubs database: 
BACKUP LOG pubs WITH TRUNCATE_ONLY


Click here for information on restoring transaction log backups.

Retrieved from "http://sqlserverpedia.com/wiki/Types_of_Backups"