BACKUP

Backup an entire database, transaction log, file(s) or filegroup(s).

Syntax
      --Backup an entire database 
	   BACKUP DATABASE {database | @database_name_var } 
	      TO backup_device [ ,...n ] 
	         [[MIRROR TO backup_device [ ,...n ] ] [ ...next-mirror ] ] 
	            [WITH options ]

      --Backup specific files or filegroups
      BACKUP DATABASE {database_name | @database_name_var }
         file_or_filegroup [ ,...f ] 
            TO backup_device [ ,...n ] 
               [[MIRROR TO backup_device [ ,...n ] ] [ ...next-mirror ] ] 
                  [WITH options ]

      --Backup a transaction log
      BACKUP LOG {database_name | @database_name_var }
       { 
	      TO backup_device [ ,...n ]
           [[MIRROR TO backup_device [ ,...n ] ] [ ...next-mirror ] ]
              [WITH options ]
       }

      --Truncate the transaction log 
      BACKUP LOG {database | @database_name_var} 
         WITH 
            {NO_LOG | TRUNCATE_ONLY}

Options:
   BLOCKSIZE = {blocksize | @blocksize_variable}
   {CHECKSUM | NO_CHECKSUM } 
   {STOP_ON_ERROR | CONTINUE_AFTER_ERROR}
   DESCRIPTION = {'text' | @text_variable}
   DIFFERENTIAL
   EXPIREDATE = {date | @date_var} | RETAINDAYS = {days | @days_var}
   PASSWORD = {password | @password_variable}
   {FORMAT | NOFORMAT }
   {INIT | NOINIT}
   {NOSKIP | SKIP}
   MEDIADESCRIPTION = {'text' | @text_variable}
   MEDIANAME = {media_name | @media_name_variable}
   MEDIAPASSWORD = {mediapassword | @mediapassword_variable}
   NO_TRUNCATE   **
   {NORECOVERY | STANDBY = undo_file_name }  **
   NAME = {backup_set_name | @backup_set_name_var}
   {NOREWIND | REWIND}
   {NOUNLOAD | UNLOAD}
   RESTART
   STATS [= percentage]
   COPY_ONLY 

      ** = option for BACKUP LOG command only
     Multiple options can be specified with or without commas to separate
     e.g. option option or option, option

file_or_filegroup :: = 
       { 
	     FILE = logical_file_name
        FILE = @logical_file_name_var
        FILEGROUP = logical_filegroup_name 
        FILEGROUP = @logical_filegroup_name_var
        READ_WRITE_FILEGROUPS
       }

backup_device ::= 
    {
    logical_backup_device_name
    @logical_backup_device_name_var
    {DISK | TAPE} = {'physical_backup_device_name' | @physical_backup_device_name_var}
	 }

The option NO_LOG /TRUNCATE_ONLY will be removed in a future version of SQL Server.

Examples (These assume the backup devices already exist)

   -- Backup the 'MySample' database to the logical backup device 'MySampleDevice'
   -- Simple recovery model 
   BACKUP DATABASE MySample
   TO MySampleDevice

   -- Back up the full 'MySample2' database to the logical backup device 'MySample2Device'.
   -- Full recovery model 
   BACKUP DATABASE MySample2 TO MySample2Device

   -- Then Backup the MySample2 log file.
   BACKUP LOG MySample2
   TO MySample2Device

"We don't want to go back to tomorrow, we want to go.. forward" - Dan Quale

Related commands:

ALTER DATABASE
DBCC SQLPERF
RESTORE DATABASE Complete
RESTORE DATABASE Partial
RESTORE DATABASE Files
RESTORE LOGS
RESTORE DATABASE_SNAPSHOT
RESTORE FILELISTONLY
RESTORE HEADERONLY
RESTORE LABELONLY
RESTORE VERIFYONLY
sp_addumpdevice
sp_configure
sp_helpfile
sp_helpfilegroup

Equivalent Oracle command:

RMAN