yeah; you can do this within tools, database utilities
but i would reccomend looking up the syntax for just doing this via
TSQL; it seems a lot more straight-forward and powerful.
here is the page from SQL Server books online-- the best source for ADP
info
------------------------------------------------------------------------
BACKUP
Backs up an entire database, transaction log, or one or more files or
filegroups. For more information about database backup and restore
operations, see Backing Up and Restoring Databases.
Syntax
Backing up an entire database:
BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
Backing up specific files or filegroups:
BACKUP DATABASE { database_name | @database_name_var }
< file_or_filegroup > [ ,...n ]
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
Backing up a transaction log:
BACKUP LOG { database_name | @database_name_var }
{
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ ,] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword |
@mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
}
< backup_device > ::=
{
{ logical_backup_device_name | @logical_backup_device_name_var
}
|
{ DISK | TAPE } =
{ 'physical_backup_device_name' |
@physical_backup_device_name_var }
}
< file_or_filegroup > ::=
{
FILE = { logical_file_name | @logical_file_name_var }
|
FILEGROUP = { logical_filegroup_name |
@logical_filegroup_name_var }
}
Truncating the transaction log:
BACKUP LOG { database_name | @database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
Arguments
DATABASE
Specifies a complete database backup. If a list of files and filegroups
is specified, only those files and filegroups are backed up.
Note During a full database or differential backup, Microsoft® SQL
Server™ backs up enough of the transaction log to produce a
consistent database for when the database is restored. Only a full
database backup can be performed on the master database.
{ database_name | @database_name_var }
Is the database from which the transaction log, partial database, or
complete database is backed up. If supplied as a variable
(@database_name_var), this name can be specified either as a string
constant (@database_name_var = database name) or as a variable of
character string data type, except for the ntext or text data types.
< backup_device >
Specifies the logical or physical backup device to use for the backup
operation. Can be one or more of the following:
{ logical_backup_device_name } | { @logical_backup_device_name_var }
Is the logical name, which must follow the rules for identifiers, of
the backup device(s) (created by sp_addumpdevice) to which the database
is backed up. If supplied as a variable
(@logical_backup_device_name_var), the backup device name can be
specified either as a string constant (@logical_backup_device_name_var
= logical backup device name) or as a variable of character string data
type, except for the ntext or text data types.
{ DISK | TAPE } =
'physical_backup_device_name' | @physical_backup_device_name_var
Allows backups to be created on the specified disk or tape device. The
physical device specified need not exist prior to executing the BACKUP
statement. If the physical device exists and the INIT option is not
specified in the BACKUP statement, the backup is appended to the
device.
When specifying TO DISK or TO TAPE, enter the complete path and file
name. For example, DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Mybackup.dat' or TAPE = '\\.\TAPE0'.
Note If a relative path name is entered for a backup to disk, the
backup file is placed in the default backup directory. This directory
is set during installation and stored in the BackupDirectory registry
key under KEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer.
If using a network server with a Uniform Naming Convention (UNC) name
or using a redirected drive letter, specify a device type of disk.
When specifying multiple files, logical file names (or variables) and
physical file names (or variables) can be mixed. However, all devices
must be of the same type (disk, tape, or pipe).
Backup to tape is not supported on Windows 98.
n
Is a placeholder that indicates multiple backup devices may be
specified. The maximum number of backup devices is 64.
BLOCKSIZE = { blocksize | @blocksize_variable }
Specifies the physical block size, in bytes. On Windows NT systems, the
default is the default block size of the device. Generally, this
parameter is not required as SQL Server will choose a blocksize that is
appropriate to the device. On Windows 2000-based computers, the default
is 65,536 (64 KB, which is the maximum size SQL Server supports).
For DISK, BACKUP automatically determines the appropriate block size
for disk devices.
Note To transfer the resulting backup set to a CD-ROM and then restore
from that CD-ROM, set BLOCKSIZE to 2048.
The default BLOCKSIZE for tape is 65,536 (64 KB). Explicitly stating a
block size overrides SQL Server's selection of a block size.
DESCRIPTION = { 'text' | @text_variable }
Specifies the free-form text describing the backup set. The string can
have a maximum of 255 characters.
DIFFERENTIAL
Specifies the database or file backup should consist only of the
portions of the database or file changed since the last full backup. A
differential backup usually takes up less space than a full backup. Use
this option so that all individual log backups since the last full
backup do not need to be applied. For more information, see
Differential Database Backups and File Differential Backups.
Note During a full database or differential backup, SQL Server backs
up enough of the transaction log to produce a consistent database when
the database is restored.
EXPIREDATE = { date | @date_var }
Specifies the date when the backup set expires and can be overwritten.
If supplied as a variable (@date_var), this date is specified as either
a string constant (@date_var = date), as a variable of character string
data type (except for the ntext or text data types), a smalldatetime,
or datetime variable, and must follow the configured system datetime
format.
RETAINDAYS = { days | @days_var }
Specifies the number of days that must elapse before this backup media
set can be overwritten. If supplied as a variable (@days_var), it must
be specified as an integer.
Important If EXPIREDATE or RETAINDAYS is not specified, expiration is
determined by the media retention configuration setting of
sp_configure. These options only prevent SQL Server from overwriting a
file. Tapes can be erased using other methods, and disk files can be
deleted through the operating system. For more information about
expiration verification, see SKIP and FORMAT in this topic.
PASSWORD = { password | @password_variable }
Sets the password for the backup set. PASSWORD is a character string.
If a password is defined for the backup set, the password must be
supplied to perform any restore operation from the backup set.
Important A backup set password protects the contents of the backup
set from unauthorized access through SQL Server 2000 tools, but does
not protect the backup set from being overwritten.
For more information about using passwords, see the Permissions
section.
FORMAT
Specifies that the media header should be written on all volumes used
for this backup operation. Any existing media header is overwritten.
The FORMAT option invalidates the entire media contents, ignoring any
existing content.
Important Use FORMAT carefully. Formatting one backup device or medium
renders the entire media set unusable. For example, if a single tape
belonging to an existing striped media set is initialized, the entire
media set is rendered useless.
By specifying FORMAT, the backup operation implies SKIP and INIT; these
do not need to be explicitly stated.
NOFORMAT
Specifies the media header should not be written on all volumes used
for this backup operation and does not rewrite the backup device unless
INIT is specified.
INIT
Specifies that all backup sets should be overwritten, but preserves the
media header. If INIT is specified, any existing backup set data on
that device is overwritten.
The backup media is not overwritten if any one of the following
conditions is met:
All backup sets on the media have not yet expired. For more
information, see the EXPIREDATE and RETAINDAYS options.
The backup set name given in the BACKUP statement, if provided, does
not match the name on the backup media. For more information, see the
NAME clause.
Use the SKIP option to override these checks. For more information
about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the
Remarks section.
Note If the backup media is password protected, SQL Server does not
write to the media unless the media password is supplied. This check is
not overridden by the SKIP option. Password-protected media may be
overwritten only by reformatting it. For more information, see the
FORMAT option.
NOINIT
Indicates that the backup set is appended to the specified disk or tape
device, preserving existing backup sets. NOINIT is the default.
The FILE option of the RESTORE command is used to select the
appropriate backup set at restore time. For more information, see
RESTORE.
If a media password is defined for the media set, the password must be
supplied.
MEDIADESCRIPTION = { text | @text_variable }
Specifies the free-form text description, maximum of 255 characters, of
the media set.
MEDIANAME = { media_name | @media_name_variable }
Specifies the media name, a maximum of 128 characters, for the entire
backup media set. If MEDIANAME is specified, it must match the
previously specified media name already existing on the backup
volume(s). If not specified or if the SKIP option is specified, there
is no verification check of the media name.
MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
Sets the password for the media set. MEDIAPASSWORD is a character
string.
If a password is defined for the media set, the password must be
supplied to create a backup set on that media set. In addition, that
media password also must be supplied to perform any restore operation
from the media set. Password-protected media may be overwritten only by
reformatting it. For more information, see the FORMAT option.
For more information about using passwords, see the Permissions
section.
NAME = { backup_set_name | @backup_set_var }
Specifies the name of the backup set. Names can have a maximum of 128
characters. If NAME is not specified, it is blank.
NORECOVERY
Used only with BACKUP LOG. Backs up the tail of the log and leaves the
database in the Restoring state. NORECOVERY is useful when failing over
to a secondary database or when saving the tail of the log prior to a
RESTORE operation.
STANDBY = undo_file_name
Used only with BACKUP LOG. Backs up the tail of the log and leaves the
database in read-only and standby mode. The undo file name specifies
storage to hold rollback changes which must be undone if RESTORE LOG
operations are to be subsequently applied.
If the specified undo file name does not exist, SQL Server creates it.
If the file does exist, SQL Server overwrites it. For more information,
see Using Standby Servers.
NOREWIND
Specifies that SQL Server will keep the tape open after the backup
operation. NOREWIND implies NOUNLOAD. SQL Server will retain ownership
of the tape drive until a BACKUP or RESTORE command is used WITH
REWIND.
If a tape is inadvertently left open, the fastest way to release the
tape is by using the following RESTORE command:
RESTORE LABELONLY FROM TAPE = <name> WITH REWIND
A list of currently open tapes can be found by querying the
sysopentapes table in the master database.
REWIND
Specifies that SQL Server will release and rewind the tape. If neither
NOREWIND nor REWIND is specified, REWIND is the default.
NOSKIP
Instructs the BACKUP statement to check the expiration date of all
backup sets on the media before allowing them to be overwritten.
SKIP
Disables the backup set expiration and name checking usually performed
by the BACKUP statement to prevent overwrites of backup sets. For more
information, see the Remarks section.
NOUNLOAD
Specifies the tape is not unloaded automatically from the tape drive
after a backup. NOUNLOAD remains set until UNLOAD is specified. This
option is used only for tape devices.
UNLOAD
Specifies that the tape is automatically rewound and unloaded when the
backup is finished. UNLOAD is set by default when a new user session is
started. It remains set until that user specifies NOUNLOAD. This option
is used only for tape devices.
RESTART
Specifies that SQL Server restarts an interrupted backup operation. The
RESTART option saves time because it restarts the backup operation at
the point it was interrupted. To RESTART a specific backup operation,
repeat the entire BACKUP statement and add the RESTART option. Using
the RESTART option is not required but can save time.
Important This option can only be used for backups directed to tape
media and for backups that span multiple tape volumes. A restart
operation never occurs on the first volume of the backup.
STATS [ = percentage ]
Displays a message each time another percentage completes, and is used
to gauge progress. If percentage is omitted, SQL Server displays a
message after each 10 percent is completed.
< file_or_filegroup >
Specifies the logical names of the files or filegroups to include in
the database backup. Multiple files or filegroups may be specified.
FILE = { logical_file_name | @logical_file_name_var }
Names one or more files to include in the database backup.
FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
Names one or more filegroups to include in the database backup.
Note Back up a file when the database size and performance
requirements make a full database backup impractical. To back up the
transaction log separately, use BACKUP LOG.
Important To recover a database using file and filegroup backups, a
separate backup of the transaction log must be provided by using BACKUP
LOG. For more information about file backups, see Backing up Using File
Backups.
File and filegroup backups are not allowed if the recovery model is
simple.
n
Is a placeholder indicating that multiple files and filegroups may be
specified. There is no maximum number of files or filegroups.
LOG
Specifies a backup of the transaction log only. The log is backed up
from the last successfully executed LOG backup to the current end of
the log. Once the log is backed up, the space may be truncated when no
longer required by replication or active transactions.
Note If backing up the log does not appear to truncate most of the
log, an old open transaction may exist in the log. Log space can be
monitored with DBCC SQLPERF (LOGSPACE). For more information, see
Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it
and truncates the log. This option frees space. Specifying a backup
device is unnecessary because the log backup is not saved. NO_LOG and
TRUNCATE_ONLY are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the
changes recorded in the log are not recoverable. For recovery purposes,
immediately execute BACKUP DATABASE.
NO_TRUNCATE
Allows backing up the log in situations where the database is damaged.
Remarks
Database or log backups can be appended to any disk or tape device,
allowing a database, and its transaction logs, to be kept within one
physical location.
SQL Server uses an online backup process to allow a database backup
while the database is still in use. The following list includes
operations that cannot run during a database or transaction log backup:
File management operations such as the ALTER DATABASE statement with
either the ADD FILE or REMOVE FILE options; INSERT, UPDATE, or DELETE
statements are allowed during a backup operation.
Shrink database or shrink file. This includes autoshrink operations.
If a backup is started when one of these operations is in progress, the
backup ends. If a backup is running and one of these operations is
attempted, the operation fails.
Cross-platform backup operations, even between different processor
types, can be performed as long as the collation of the database is
supported by the operating system. For more information, see SQL Server
Collation Fundamentals.
Backup File Format
SQL Server backups can coexist on tape media with Windows NT backups
because the SQL Server 2000 backup format conforms to Microsoft Tape
Format (MTF); the same format used by Windows NT tape backups. To
ensure interoperability, the tape should be formatted by NTBackup.
Backup Types
Backup types supported by SQL Server include:
Full database backup, which backs up the entire database including the
transaction log.
Differential database backup performed between full database backups.
Transaction log backup.
A sequence of log backups provides for a continuous chain of
transaction information to support recovery forward from database,
differential, or file backups.
File(s) and Filegroup(s) backup.
Use BACKUP to back up database files and filegroups instead of the full
database when time constraints make a full database backup impractical.
To back up a file instead of the full database, put procedures in place
to ensure that all files in the database are backed up regularly. Also,
separate transaction log backups must be performed. After restoring a
file backup, apply the transaction log to roll the file contents
forward to make it consistent with the rest of the database.
Backup devices used in a stripe set must always be used in a stripe set
(unless reinitialized at some point with FORMAT) with the same number
of devices. After a backup device is defined as part of a stripe set,
it cannot be used for a single devicebackup unless FORMAT is specified.
Similarly, a backup device that contains nonstriped backups cannot be
used in a stripe set unless FORMAT is specified. Use FORMAT to split a
striped backup set.
If neither MEDIANAME nor MEDIADESCRIPTION is specified when a media
header is written, the media header field corresponding to the blank
item is empty.
BACKUP LOG cannot be used if the recovery model is SIMPLE. Use BACKUP
DATABASE instead.
Interaction of SKIP, NOSKIP, INIT, and NOINIT
This table shows how the { INIT | NOINIT } and { NOSKIP | SKIP }
clauses interact.
Note In all these interactions, if the tape media is empty or the disk
backup file does not exist, write a media header and proceed. If the
media is not empty and does not contain a valid media header, give
feedback that this is not valid MTF media and abort the backup.
INIT NOINIT
SKIP If the volume contains a valid1 media header, verify the media
password and overwrite any backup sets on the media, preserving only
the media header.
If the volume does not contain a valid media header, generate one with
the given MEDIANAME, MEDIAPASSWORD, and MEDIADESCRIPTION, if any.
If the volume contains a valid media header, verify the media password
and append the backup set, preserving all existing backup sets.
If the volume does not contain a valid media header, an error occurs.
NOSKIP If the volume contains a valid media header, perform the
following checks:
Verify the media password.2
If MEDIANAME was specified, verify that the given media name matches
the media header's media name.
Verify that there are no unexpired backup set(s) already on the media.
If there are, abort the backup.
If these checks pass, overwrite any backup sets on the media,
preserving only the media header.
If the volume does not contain a valid media header, generate one with
the given MEDIANAME, MEDIAPASSWORD, and MEDIADESCRIPTION, if any.
If the volume contains a valid media header, verify the media
password* and verify that the media name matches the given MEDIANAME,
if any. If it matches, append the backup set, preserving all existing
backup sets.
If the volume does not contain a valid media header, an error occurs.
1. Validity includes the MTF version number and other header
information. If the version specified is unsupported or an unexpected
value, an error occurs.
2. The user must belong to the appropriate fixed database or server
roles and provide the correct media password to perform a backup
operation.
Note To maintain backward compatibility, the DUMP keyword can be used
in place of the BACKUP keyword in the BACKUP statement syntax. In
addition, the TRANSACTION keyword can be used in place of the LOG
keyword.
Backup History Tables
SQL Server includes these backup history tables that track backup
activity:
backupfile
backupmediafamily
backupmediaset
backupset
When a RESTORE is performed, the backup history tables are modified.
Compatibility Considerations
Caution Backups created with Microsoft® SQL Server™ 2000 cannot be
restored in earlier versions of SQL Server.
Permissions
BACKUP DATABASE and BACKUP LOG permissions default to members of the
sysadmin fixed server role and the db_owner and db_backupoperator fixed
database roles.
In addition, the user may specify passwords for a media set, a backup
set, or both. When a password is defined on a media set, it is not
enough that a user is a member of appropriate fixed server and database
roles to perform a backup. The user also must supply the media password
to perform these operations. Similarly, restore is not allowed unless
the correct media password and backup set password are specified in the
restore command.
Defining passwords for backup sets and media sets is an optional
feature in the BACKUP statement. The passwords will prevent
unauthorized restore operations and unauthorized appends of backup sets
to media using SQL Server 2000 tools, but passwords do not prevent
overwrite of media with the FORMAT option.
Thus, although the use of passwords can help protect the contents of
media from unauthorized access using SQL Server tools, passwords do not
protect contents from being destroyed. Passwords do not fully prevent
unauthorized access to the contents of the media because the data in
the backup sets is not encrypted and could theoretically be examined by
programs specifically created for this purpose. For situations where
security is crucial, it is important to prevent access to the media by
unauthorized individuals.
It is an error to specify a password for objects that were not created
with associated passwords.
BACKUP creates the backup set with the backup set password supplied
through the PASSWORD option. In addition, BACKUP will normally verify
the media password given by the MEDIAPASSWORD option prior to writing
to the media. The only time that BACKUP will not verify the media
password is when it formats the media, which overwrites the media
header. BACKUP formats the media only:
If the FORMAT option is specified.
If the media header is invalid and INIT is specified.
If the operation is writing a continuation volume.
If BACKUP writes the media header, BACKUP will assign the media set
password to the value specified in the MEDIAPASSWORD option.
For more information about the impact of passwords on SKIP, NOSKIP,
INIT, and NOINIT options, see the Remarks section.
Ownership and permission problems on the backup device's physical file
can interfere with a backup operation. SQL Server must be able to read
and write to the device; the account under which the SQL Server service
runs must have write permissions. However, sp_addumpdevice, which adds
an entry for a device in the system tables, does not check file access
permissions. Such problems on the backup device's physical file may not
appear until the physical resource is accessed when the backup or
restore is attempted.
Examples
A. Back up the entire MyNwind database
Note The MyNwind database is shown for illustration only.
This example creates a logical backup device in which a full backup of
the MyNwind database is placed.
-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
B. Back up the database and log
This example creates both a full database and log backup. The database
is backed up to a logical backup device called MyNwind_2, and then the
log is backed up to a logical backup device called MyNwindLog1.
Note Creating a logical backup device needs to be done only once.
-- Create the backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_2',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_2.dat'
--Create the log backup device.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwindLog1',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwindLog1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_2
-- Update activity has occurred since the full database backup.
-- Back up the log of the MyNwind database.
BACKUP LOG MyNwind
TO MyNwindLog1
See Also
Backup Formats
DBCC SQLPERF
RESTORE
RESTORE FILELISTONLY
RESTORE HEADERONLY
RESTORE LABELONLY
RESTORE VERIFYONLY
sp_addumpdevice
sp_configure
sp_dboption
sp_helpfile
sp_helpfilegroup
Using Identifiers
Using Media Sets and Families
