Database Backups

When you back up a database, determine whether to overwrite or append to a backup file.
The SQL Server default is to append (NOINIT) backups to a file. If you use the NOINIT
option, SQL Server appends a backup to an existing backup file or backup set.

If you use the INIT option, SQL Server overwrites any existing data on the backup media
set, but it retains the header information. If the first file of the backup set on the device has an ANSI-standard label, SQL Server determines whether the previous backup set can be overwritten. 

Use the FORMAT option to overwrite the contents of a backup file and split up the
backup set.


--Backup device

USE master
EXEC sp_addumpdevice 'disk', 'AdventureWorksBack','D:\MyBackupDir\AdventureWorksBack.bak'

--to see the media contents of the backup device

RESTORE filelistonly from AdventureWorksBack
RESTORE HEADERONLY from AdventureWorksBack

--full backup

--Full backup on backup device

BACKUP DATABASE AdventureWorks TO AdventureWorksBack

--a full database backup to the AdventureWorksBack file and overwrites any previous backups --on that file

BACKUP DATABASE AdventureWorks TO AdventureWorksBack WITH INIT

--appends a full database backup to the AdventureWorksBack file.Any previous backup files --are left intact.

BACKUP DATABASE AdventureWorks TO AdventureWorksBack WITH NOINIT

--creates a backup disk file and performs a full database backup to that file.

BACKUP DATABASE AdventureWorks TO
DISK = 'D:\Temp\MyTempBackup.bak'


--Transcation LOg Backup

--CREATING another backup device for t-log backups

USE master
EXEC sp_addumpdevice 'disk', 'AWBackLog',
'D:\Backup\AWBackLog.bak'

--backing up the t-log into device

BACKUP LOG AdventureWorks TO AWBackLog

--backing up the t-log into disk

BACKUP LOG AdventureWorks TO
DISK = 'D:\Temp\MyTempBackup.bak'


--Performing a Tail-log Backup

/*
If a database’s data files are unavailable, but the log file is undamaged, you can perform a tail-log backup to capture database activity since the last backup and use it to restore the database to the point of failure.
*/

BACKUP LOG AdventureWorks
TO DISK = 'C:\Backup\AWTail.bak'
WITH NORECOVERY, NO_TRUNCATE


--Differential Backups

--CREATING another backup device for tDifferential backups

USE master
EXEC sp_addumpdevice 'disk', 'AWBackdiff',
'D:\Backup\AWBackdiff.bak'


--to disk files

BACKUP DATABASE AdventureWorks TO
DISK = 'D:\MyData\MyDiffBackup.bak'
WITH DIFFERENTIAL

--to device

BACKUP DATABASE AdventureWorks TO AWBackdiff
WITH DIFFERENTIAL


--File or Filegroup Backups

BACKUP DATABASE PhoneOrders
FILE = Orders2 TO OrderBackup2
BACKUP LOG PhoneOrders to OrderBackupLog

/*

Partial Backups

Similar to full back up, it takes the backup of primary

To create a partial backup, include the READ_WRITE_FILEGROUPS option in your
BACKUP statement. Using the READ_WRITE_FILEGROUPS option causes SQL
Server to back up the primary filegroup and all read/write filegroups. You can also
include read-only files in a partial backup by listing them explicitly in the BACKUP
command.

*/

BACKUP DATABASE AdventureWorks READ_WRITE_FILEGROUPS TO AWBackup

--Ensuring Backup Integrity

--Backup Mirroring

/*
New in sql server 2005

SQL Server supports mirroring of backup media, increasing the reliability of backups by
providing redundancy.

Mirroring a media set increases backup reliability by reducing the
impact of backup-device malfunctions.

These malfunctions are particularly serious because backups are the last line of defense against data loss.

Mirroring applies to both disk and tape.

All backup devices for a single backup or restore operation must be of the same type—disk or tape. Within these broader classes, you must use similar devices that have the same properties, such as drives with the same model number from the same manufacturer.
Insufficiently similar devices generate an error message (3212).
*/

BACKUP DATABASE AdventureWorks
TO BackupOrginal
MIRROR TO BackupMirror
WITH
FORMAT

--Backup Checksum

BACKUP DATABASE [AdventureWorks] TO AWBackup WITH CHECKSUM

--RESTORE VERIFYONLY

/*

Checks performed by RESTORE VERIFYONLY include:
• Ensuring that the backup set is complete and all volumes are readable.
• Ensuring the integrity of some header fields of database pages, such as the page ID
(as if it were about to write the data).
• Ensuring the validity of the checksum (if present on the media).
• Ensuring that there is sufficient space on destination devices.

*/

RESTORE VERIFYONLY FROM AWBackup