Taking Different types of Backups in SQL Server using Queries

We can take different types of backup in SQL Server using the following T-SQL queries.

Backup file extensions are .bak and .trn

The following syntaxes for taking

1. full backup,

2. Differential backup,

3. Transactional-log/T-log backup,

4. File and filegroup backup,

5. Mirror backup,

6.Striped backup, and

7. copy only backup.


1. Syntax for full backup:   

backup database <db-name> to disk='<path-name>’

Example: 

backup database TestDB  to disk=’D:\Backups\fullback 1.bak’


2. Syntax for differential backup:   

backup database <db-name> to disk='<path-name>’ with differential

Example: 

backup database TestDB  to disk=’D:\Backups\diffbackup11.bak’  with differential


3. Syntax for T-Log/ Transactional log backup:   

backup log <db-name> to disk='<path-name>’

Example: 

backup log TestDB  to disk=’D:\Backups\tranlog1.trn’


4. Syntax for file and filegroup backup:   

backup database <db-name> file=’primary/file-name’ to disk='<path-name>’

backup database <db-name> filegroup=’primary/fg-name’ to disk='<path-name>’

Example: 

backup database TestDB file=’File1′ to disk=’D:\Backups\filebackup1.bak’

backup database TestDB filegroup=’primary’ to disk=’D:\Backups\fgback 1.bak’


5. Syntax for Mirror backup:   

backup database <db-name> to disk='<path-name>’  mirror to  disk='<path-name>’ with format

Example: 

backup database TestDB  to disk=’D:\Backups\mirrorbackup1.bak’ mirror to disk=’D:\Backups\mirrorback2.bak’ with format


6. Syntax for Striped backup:   

backup database <db-name> to disk='<path-name1>’, disk='<path-name2>’

Example: 

backup database TestDB  to disk=’D:\Backups\stripedbackup1.bak’, disk=’D:\Backups\stripedbackup2.bak


7. Syntax for Copy only backup:   

backup database <db-name> to disk='<path-name>’ with copy_only

Example: 

backup database TestDB  to disk=’D:\Backups\copyonlybackup 1.bak’ with copy_only


8. Syntax for Tail-Log backup:   

backup database <db-name> to disk='<path-name>’ with  no_truncate

Example: 

backup database TestDB  to disk=’D:\Backups\taillogbackup1.trn’ with no_truncate


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s