SQL Database Backup

Backup Database :

The basic Transact-SQL syntax for a full database backup is:

Syntax:

BACKUP DATABASE database TO backup_device [ ,...n ]  [ WITH with_options [ ,...o ] ] ;

Ex1:-

BACKUP DATABASE TESTDB to DISK = ' G:\MSSQL_DEFAULT\Backup\TESTDB_FULL_Backup_060220150118PM.bak'

Ex2:-

USE AdventureWorks;
GO
BACKUP DATABASE AdventureWorks
TO DISK = 'Z:\SQLServerBackups\AdventureWorks.Bak'
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks';
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`~~~~

Option  Description 

database

    Is the database that is to be backed up.

backup_device [ ,...n ]  

   Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a physical backup device, or you can specify a corresponding logical backup device, if already defined. To specify a physical backup device, use the DISK or TAPE option:

{ DISK | TAPE } = physical_backup_device_name

For more information, see Backup Devices.

WITH with_options [ ,...o ]

 Optionally, specifies one or more additional options, o. For information about some of the basic with options, see step 2.