Backup Database with all Backup Types

Backing up a full database backup

BACKUP DATABASE CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB.bak'
WITH FORMAT;
GO

Backing up Full backup and differntial backup

BACKUP DATABASE CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB.bak'
WITH FORMAT;
GO

BACKUP DATABASE CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB_Diff.bak'
WITH DIFFERENTIAL
GO

Backing up a full database backup and log backup

To take Log backup m it is require to set database to Full recovery mode.

alter database CompleteDB set recovery full

BACKUP DATABASE CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB.bak'
WITH FORMAT
GO

BACKUP log CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB.trn'
GO

Backing up full file backup of filegroups

First we are creating filegroup

alter database CompleteDB
add filegroup SecondaryFile

then we are adding file on that filegroup

alter database CompleteDB
add file
(
name = 'SecondaryFile' ,
Filename = 'c:\CompleteDB_Secondary_File.ndf'
)
TO FILEGROUP SecondaryFile

Now we are taking backup of filegroup

Backup database CompleteDB
filegroup = 'SecondaryFile'
to disk = 'C:\BackupDir\SecondaryFile.bck'

we can also take differntail backup of filegroup

Backup database CompleteDB
filegroup = 'SecondaryFile'
to disk = 'C:\BackupDir\SecondaryFile.bck'
WITH DIFFERENTIAL



Taking backup on BumpDevice

First we create dumpdevice

USE master
GO

EXEC sp_addumpdevice 'disk', 'BumpDevice',
'C:\backupdir\BumpDevice.bak';
GO

Then we take full database backup on that device

BACKUP DATABASE CompleteDB
TO BumpDevice
GO