Different Types Of Backups In SQL Server

Different Types Of Backups In SQL Server

Create Full backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak'

Create Differential backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksdiff.bak' WITH DIFFERENTIAL

Create Transaction Log backup

Backup Log Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworkslog.trn'

Create COPY ONLY Full Backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak' WITH COPY_ONLY

Create COPY ONLY Differential Backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak' WITH DIFFERENTIAL, COPY_ONLY

Create COPY ONLY Log Backup

Backup log Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.trn' WITH COPY_ONLY

Create Tail-Log Backup if Database is Doesn't Start/Suspect mode

Backup Log Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworkslog.trn' WITH {CONTINUE_AFTER_ERROR | NO_TRUNCATE}

Create Tail-Log Backup if Database is Online

Backup Log Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworkslog.trn' WITH norecovery

Create FILE Backup

Backup database Adventureworks 
FILE= 'file name' 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak'

Create FILEGROUP Backup 
Backup database Adventureworks

FILEGROUP= 'filegroupname' 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak'

Create MIRRORED Backup

Backup database Adventureworks 
To Disk = 'D:\Projects\BACKUPS\New folder\adventureworksfull.bak' 
MIRROR 
To Disk = 'E:\Projects\BACKUPS\New folder\adventureworksfull.bak' 

WITH FORMAT

The supported backup types depend on the recovery model of the database, as follows.

Scope of backup	Backup types
Whole database	Database backups cover the whole database.
Partial database	Partial backups cover read/-write filegroups and, possibly, one or more read-only files or filegroups.
File or filegroup	File backups cover one or more files or filegroups, and are relevant only for databases that contain multiple filegroups. Under the simple recovery model, file backups are essentially restricted to read-only secondary filegroups.