syntaxhelp.com Your online cheat sheet!

Creating a Full Database Backup to a Disk File

-- Database Name: AdventureWorks
BACKUP DATABASE AdventureWorks 
     TO DISK = 'c:\temp\AdventureWorks.bak'
GO

Creating a Full Backup with COPY ONLY Option

-- Database Name: AdventureWorks
BACKUP DATABASE AdventureWorks 
     TO DISK = 'c:\temp\AdventureWorks.bak'
     WITH COPY_ONLY
GO

Creating a Compressed Backup in SQL Server 2008

-- Database Name: AdventureWorks
BACKUP DATABASE Adventureworks 
     TO DISK = 'c:\temp\AdventureWorks.bak'
     WITH COMPRESSION 

Creating Backup Device for disk

-- Set devtype to 'Disk' to create a disk backup device
USE master
go
EXEC sp_addumpdevice   
         @devtype = 'Disk',
         @logicalname =  'AdventureWorks',
         @physicalname = 'D:\AdventureWorks.bak'

Creating Backup Device for Network Path

USE master
go
EXEC sp_addumpdevice	
         @devtype = 'Disk',
         @logicalname =  'NetworkDevice',
         @physicalname =\\RemServer\SharedPath\AdventureWorks.bak'

Creating Backup Device for Tape

-- Set devtype to 'Tape' to create a tape backup device
USE master
go
EXEC sp_addumpdevice	
          @devtype = 'Tape',			
          @logicalname =  'tapedump1',
          @physicalname = '\\.\tape0'

Listing all the Backup Devices

SELECT * FROM sys.sysdevices

Taking a Backup on a Backup Device

BACKUP DATABASE AdventureWorks TO AdventureWorks

Taking a Backup on a Backup Device with FORMAT

--'FORMAT' option creates new media set  by Formatting Backup Device
BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH FORMAT;

Taking a Backup on a Backup Device with NOINIT

--'NOINIT' option appends Backup to Backup Device - (Default behaviour)
BACKUP DATABASE AdventureWorks TO AdventureWorks
[WITH NOINIT];

Taking a Backup on a Backup Device with INIT

--'INIT' option overwrites the backup on the Backup Device
BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH INIT;

Taking a Backup on a Backup Device and to Remote path

BACKUP DATABASE AdventureWorks TO AdventureWorks
Mirror To Disk = 'D:\AdventureWorks_Mirror.bak'
WITH FORMAT;

Taking a Backup on a Backup Device and to Multiple Remote paths

-- Backup can be mirrored to maximum three remote paths
BACKUP DATABASE AdventureWorks TO AdventureWorks
Mirror To Disk = 'D:\AdventureWorks_Mirror.bak'
Mirror To Disk = 'E:\AdventureWorks_Mirror.bak'
Mirror To Disk = 'F:\AdventureWorks_Mirror.bak'
WITH FORMAT;

Taking Split Backups

--Backup can be split up to maximum 64 devices
BACKUP DATABASE AdventureWorks TO
Disk = 'D:\AdventureWorks_1.bak',
Disk = 'E:\AdventureWorks_2.bak',
Disk = 'F:\AdventureWorks_3.bak'
WITH FORMAT;

Taking Split Backups and Copying them to a Remote path

--Both the Local and Mirror backup split devices should be same
BACKUP DATABASE AdventureWorks TO
Disk = 'D:\AdventureWorks_1.bak',
Disk = 'E:\AdventureWorks_2.bak',
Disk = 'F:\AdventureWorks_3.bak'
Mirror To Disk = 'D:\AdventureWorks_Mirror1.bak',
Disk = 'E:\AdventureWorks_Mirror2.bak',
Disk = 'F:\AdventureWorks_Mirror3.bak'
WITH FORMAT;

Taking a Backup on a Backup Device protected with Password

-- Specifying password do not allow to restore the database without entering the correct password
BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH FORMAT,
PASSWORD='DIVYA';

Taking a Backup on a Backup Device with Expiry date

----Do not allow to overwrite backup device until expire date set
BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH FORMAT,
EXPIREDATE = '12/10/2009';

Taking a Backup on a Backup Device with Retain Days

--Do not allow to overwrite backup device until four days
BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH FORMAT,
RETAINDAYS = 4;

Taking a Backup on Backup Device with Skip Option

--Allows to overwrite backup device although the expiredate is set
BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH INIT, SKIP;

Taking Backup on a Backup Device with NoSkip Option

--Do not allow to overwrite backup device if the expiredate is set
BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH INIT, NOSKIP;

Taking a Backup on a Backup Device with Media Name

BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH FORMAT,
MEDIANAME = 'AdventureWorks-Media';

Taking a Backup on a Backup Device with Media Name and password

-- Specifying Mediapassword would not allow other backup 
-- to be appended to the backup device without the correct the 
-- Mediapassword

BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH FORMAT,
MEDIANAME = 'AdventureWorks-Media',
MEDIAPASSWORD = 'DIVYA';

Appending Backup on a Backup Device with Media Name and password

--Do not allow to append backup if correct mediapassword is not passed
BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH NOINIT,
MEDIANAME = 'AdventureWorks-Media',
MEDIAPASSWORD = 'DIVYA';

Appending Backup on Backup Device with Media Name and password with STATS

--'STATS' option display the backup completion statistics in percentage
BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH NOINIT,
MEDIANAME = 'AdventureWorks-Media',
MEDIAPASSWORD = 'DIVYA',
STATS = 10;

Taking Backup on Backup Device while verifying it

BACKUP DATABASE AdventureWorks TO AdventureWorks
WITH FORMAT,
CHECKSUM;