BackUp DataBase Commands....

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=NISHI;

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 = 'NISHI';

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 = 'NISHI';

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;