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


© Copyright SS64.com 1999-2014
Some rights reserved