Thursday, April 29, 2010

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.
Single table restore from backup. table backup and restore. how to do table restore from backup file. how to take single table backup. how to restore single table from old backup file. What are the types of backups in sql server 2008 R2. What are the types of backups in sql server 2012. What are the types of backups in sql server 2005. how many types of backups in sql server 2008.   how many types of backups in sql server 2012. how many types of backups in sql server 2008 R2. how many types of backups in sql server 2005. how many types of restores in sql server 2008. how many types of restores in sql server 2012. how many types of restores in sql server 2005. native backups in sql server, compressed backup.

0 comments:

Post a Comment