Anything you can do in the GUI, you can do using
T-SQL. Actually, the only way to specify certain options and perform
certain types of backups is by using T-SQL. Using the GUI is a good way
to perform a quick backup or script out a BACKUP command that you can tweak a little using T-SQL to get exactly what you are looking for. I always script out my BACKUP
statements that are generated by the GUI and review them before
actually executing the command in order to make sure the statement is
doing exactly what I am expecting. The complete syntax for creating a
database backup is shown in Listing 1.
Example 1. Complete Syntax Used for Creating Database Backups
--BACKUP STATEMENTS
--Backup database
BACKUP DATABASE { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]
--Backup files or filegroups
BACKUP DATABASE { database_name | @database_name_var }
<file_or_filegroup> [ ,...n ]
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]
--Partial backup
BACKUP DATABASE { database_name | @database_name_var }
READ_WRITE_FILEGROUPS [ , <read_only_filegroup> [ ,...n ] ]
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ]
[;]
--Transaction log backup
BACKUP LOG { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { <general_WITH_options> | <log-specific_optionspec> } [ ,...n ] ]
[;]
--BACKUP DESTINATION OPTIONS
<backup_device>::=
{
{ logical_device_name | @logical_device_name_var }
| { DISK | TAPE } =
{ 'physical_device_name' | @physical_device_name_var }
}
<MIRROR TO clause>::=
MIRROR TO <backup_device> [ ,...n ]
<file_or_filegroup>::=
{
FILE = { logical_file_name | @logical_file_name_var }
| FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
}
<read_only_filegroup>::=
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
--WITH OPTIONS
--Backup set options
COPY_ONLY
| { COMPRESSION | NO_COMPRESSION }
| DESCRIPTION = { 'text' | @text_variable }
| NAME = { backup_set_name | @backup_set_name_var }
| PASSWORD = { password | @password_variable }
| { EXPIREDATE = { 'date' | @date_var }
| RETAINDAYS = { days | @days_var } }
--Media set options
{ NOINIT | INIT }
| { NOSKIP | SKIP }
| { NOFORMAT | FORMAT }
| MEDIADESCRIPTION = { 'text' | @text_variable }
| MEDIANAME = { media_name | @media_name_variable }
| MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Data transfer options
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
--Error management options
{ NO_CHECKSUM | CHECKSUM }
| { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
--Compatibility options
RESTART
--Monitoring options
STATS [ = percentage ]
--Tape options
{ REWIND | NOREWIND }
| { UNLOAD | NOUNLOAD }
--Log-specific options
{ NORECOVERY | STANDBY = undo_file_name }
| NO_TRUNCATE
