Different Backup options in SQL Server 2008:
1. Backup’s can be taken for a complete database, or one or more files or filegroups.
2. If the Databases are in Full recovery model or bulk-logged recovery model(discussed below) then backup’s can be taken for the transaction log (BACKUP LOG).
MIRROR TO Statement: Specifies a set of up to three secondary backup devices, each of which will mirror the backups devices specified in the TO clause. The MIRROR TO clause must be specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three.
Backup Set Options
COPY_ONLY: Specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database
COMPRESSION or NO_COMPRESSION: Specifies whether backup compression is performed on this backup. Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions.
PASSWORD =: Sets the password for the backup set. PASSWORD is a character string.
EXPIREDATE or RETAINDAYS: Specifies when the backup set for this backup can be overwritten. If these options are both used, RETAINDAYS takes precedence over EXPIREDATE. These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic.
Media Set Options :These options operate on the media set as a whole.
NOINIT or INIT: It specifies if a backup operation needs to appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).
NOSKIP or SKIP: Controls whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them. The default is to the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten(NOSKIP)
NOFORMAT or FORMAT: Specifies whether the media header should be written on the volumes used for this backup operation, overwriting any existing media header and backup sets.
The above options are the one frequently used. There are many more options which can be handy and found at URL below:
While taking Backup’s,one use the Disk location or a Device which can be declared using the code below:
EXEC sp_addumpdevice 'disk', 'AdvWorksData',
1.Creating a Full Backup of the database:
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:SQLServerBackupsAdvWorksData.bak'
BACKUP DATABASE AdventureWorks2008R2 TO DISK = 'Z:SQLServerBackupsAdvWorksData.bak'
-- Back up the AdventureWorks2008R2 log.
BACKUP LOG AdventureWorks2008R2
TO DISK = 'Z:SQLServerBackupsAdvWorksData.trn'
2.Creating File Group Backups:
BACKUP DATABASE Sales
FILEGROUP = 'SalesGroup1',
FILEGROUP = 'SalesGroup2'
TO DISK = 'Z:SQLServerBackupsSalesFiles.bck'
3.Creating Differential backups:
BACKUP DATABASE Sales
TO DISK = 'Z:SQLServerBackupsSalesFiles.bak'
4.Creating a compressed backup in a new media set
BACKUP DATABASE AdventureWorks2008R2 TO DISK='Z:SQLServerBackupsAdvWorksData.bak'