论坛精华
>> SQL之家
>> 备份SQL数据库SQL语句语法
由 amtd 发布于: 2001-02-15 09:47
Caution Backups created with Microsoft® SQL Server™ version 7.0 cannot be restored in earlier versions of
SQL Server.
--------------------------------------------------------------------------------
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}]
[[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] [NAME = {backup_set_name | @backup_set_name_var}]
[[,] {NOSKIP | SKIP}]
[[,] {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}]
[[,] EXPIREDATE = {date | @date_var}
| RETAINDAYS = {days | @days_var}]
[[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] [NAME = {backup_set_name | @backup_set_name_var}]
[[,] {NOSKIP | SKIP}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] [RESTART]
[[,] STATS [= percentage]]
]
Backing up a transaction log:
BACKUP LOG {database_name | @database_name_var}
{
[WITH
{ NO_LOG | TRUNCATE_ONLY }]
}
|
{
TO <backup_device> [,...n]
[WITH
[BLOCKSIZE = {blocksize | @blocksize_variable}]
[[,] DESCRIPTION = {text | @text_variable}]
[[,] EXPIREDATE = {date | @date_var}
| RETAINDAYS = {days | @days_var}]
[[,] FORMAT | NOFORMAT]
[[,] {INIT | NOINIT}]
[[,] MEDIADESCRIPTION = {text | @text_variable}]
[[,] MEDIANAME = {media_name | @media_name_variable}]
[[,] [NAME = {backup_set_name | @backup_set_name_var}]
[[,] NO_TRUNCATE]
[[,] {NOSKIP | SKIP}]
[[,] {NOUNLOAD | UNLOAD}]
[[,] [RESTART]
[[,] STATS [= percentage]]
]
}
<backup_device> ::=
{
{backup_device_name | @backup_device_name_var}
|
{DISK | TAPE | PIPE} =
{'temp_backup_device' | @temp_backup_device_var}
}
<file_or_filegroup> ::=
{
FILE = {logical_file_name | @logical_file_name_var}
|
FILEGROUP = {logical_filegroup_name | @logical_filegroup_name_var}
}
Arguments
DATABASE
Specifies the complete backup of the database. 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, 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 being 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 permanent or temporary backup device to use for the backup operation. Can be one or more of
the following:
{backup_device_name} | {@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 (@backup_device_name_var),
the backup device name can be specified either as a string constant (@backup_device_name_var = backup
device name) or as a variable of character string data type, except for the ntext or text data types.
{ DISK | TAPE | PIPE } =
'temp_backup_device' | @temp_backup_device_var
Allows backups to be created on the named disk, tape, or pipe device. When using a temporary disk backup
device, the temporary disk backup devices must not exist prior to specifying the BACKUP statement. When
using a tape backup device, specify WITH FORMAT to ensure that the backup device is a valid Microsoft Tape
Format (MTF) data set.
--------------------------------------------------------------------------------
Important Use FORMAT carefully because it overwrites all contents on the current tape.
--------------------------------------------------------------------------------
With disk and tape, specify the complete path and file name. For example, DISK = ‘C:\Mssql7
\Backup\Mybackup.dat’ or TAPE = \\.\TAPE0. With pipe, specify the name of the named pipe that will be
used by the client application. If supplied as a variable (@temp_backup_name_var), the temporary backup
device name can be specified as a string constant or as a variable of character string data type, except
for the ntext or text data types.
Pipe files have been added to allow third-party vendors a flexible and powerful way to connect their own
software. For typical Transact-SQL use, pipe files are not used.
--------------------------------------------------------------------------------
Note When performing a backup to a named pipe, you may need to adjust the number of minutes that SQL
Server waits for the client to connect before reporting a failure. To adjust this time-out interval from
the default of 2 minutes, create the BackupNamedPipeConnectTimeout registry key and add the following
registry value:
HKEY_LOCAL_MACHINE
\SOFTWARE
\Microsoft
\MSSQLServer
Data Type: REG_DWORD
Data: Value other than the default of 2
Radix: Decimal
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
If you are using a network server with a UNC name or using a redirected drive letter, specify a device
type of disk.
--------------------------------------------------------------------------------
Important When specifying multiple files, logical file names (or variables) and temporary file names (or
variables) can be mixed. However, all devices must be of the same type (disk, tape, or pipe).
--------------------------------------------------------------------------------
n
Is a placeholder indicating that multiple backup devices may be specified. The maximum number of backup
devices is 32.
BLOCKSIZE = {blocksize | @blocksize_variable}
Specifies the physical block size, in bytes.
For DISK, BACKUP automatically determines the appropriate block size for disk devices.
--------------------------------------------------------------------------------
Note If you intend to transfer the resulting backup set to a CD-ROM and then restore from that CD-ROM, you
must set BLOCKSIZE to 2048.
--------------------------------------------------------------------------------
For tape media, BLOCKSIZE only applies if the tape is being overwritten by using FORMAT. When using tape
media, the backup operation selects an appropriate block size. Explicitly stating a block size overrides
the backup operation’s selection of a block size.
For PIPE, the backup operation uses 65,536 unless BLOCKSIZE is specified.
DESCRIPTION = {text | @text_variable}
Specifies the free-form text describing the backup set. Can be a maximum of 255 characters.
DIFFERENTIAL
Specifies the database backup should consist only of the portions of the database that have changed since
the last full backup. A differential database backup usually takes up less space than a full database. Use
this option so that all individual log backups since the last full database backup do not need to be
applied. For more information, see Differential Database Backups.
The DIFFERENTIAL option is specified for full database backups only.
--------------------------------------------------------------------------------
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 can be 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 it must follow the configured datetime format for the system. This option is used for disk
and tape devices only and is effective only when specified with all backup sets on the media.
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. This option is used for disk and tape
devices only and is effective only when specified with INIT. Specifying SKIP overrides this option.
--------------------------------------------------------------------------------
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 still be erased using other methods, and disk files can still be deleted through the operating
system.
--------------------------------------------------------------------------------
FORMAT
Specifies that the media header should be written on all volumes used for this backup operation and
rewrites the backup device. Any existing media header is overwritten. The FORMAT option also invalidates
the entire media contents, ignoring any existing password.
--------------------------------------------------------------------------------
Important Use FORMAT carefully. Initializing one backup device or medium renders the entire backup set it
contains unusable. For example, if a single tape belonging to an existing striped backup set is
initialized, the entire backup 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 the backup set should be the first file on the disk or tape device and preserves the media
header. If INIT is specified, any existing data on that device is overwritten.
The backup media is not overwritten if either 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 clause 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 or encrypted, SQL Server does not write to the media. To
overwrite media that is either password-protected or encrypted, specify the WITH FORMAT option.
--------------------------------------------------------------------------------
NOINIT
Specifies the backup set is appended to the current disk or tape device. NOINIT is the default.
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.
--------------------------------------------------------------------------------
Note If FORMAT is specified, MEDIANAME specifies the media name that is written. In addition, tapes shared
between SQL Server database backups and Microsoft Windows NT® backups must have a nonnull MEDIANAME.
--------------------------------------------------------------------------------
NAME = {backup_set_name | @backup_set_var}
Specifies the name of the backup set. Names are limited to 128 characters. If NAME is not specified, it is
blank.
NOSKIP
Instructs the BACKUP statement to check the expiration date and name of all backup sets on the media
before allowing them to be overwritten.
SKIP
Disables the backup set expiration and name checking, which is 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 the backup operation that was interrupted. The RESTART option saves
time because it restarts the backup operation at the point it was interrupted. To RESTART a specific
backup operation that was interrupted, 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 every time another percentage completes, and is used to gauge progress. If percentage
is omitted, SQL Server displays a message after every 10 percent 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 Creating File or
Filegroup Backups.
--------------------------------------------------------------------------------
File and filegroup backups are disallowed if trunc. log on chkpt. is enabled for the database. If trunc.
log on chkpt. is enabled, the log has been truncated and the log backups required to restore individual
files and filegroups are not available.
The BACKUP statement requires that entire filegroups affected by a CREATE INDEX statement since the last
filegroup backup be backed up as a unit:
If an index is created on a filegroup, that entire filegroup must be backed up in a single backup
operation. Backups of individual files that are part of the affected filegroup are not allowed.
If an index is created on a filegroup different from the one that the table resides in, then both
filegroups (the filegroup containing the table and the filegroup containing the newly created index) must
be backed up. If more than one index is created on a filegroup different from the filegroup in which the
table resides, all filegroups must immediately be backed up to accommodate these differing filegroups.
The BACKUP statement detects all of these filegroup situations and communicates to the backup user the
minimum filegroups that must be backed up.
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. By default, SQL Server truncates the log up to the beginning of
the active portion of the log, which contains the oldest open transaction.
--------------------------------------------------------------------------------
Note If backing up the log does not appear to truncate most of the log, you may have a very old open
transaction in the log. Log space can be monitored with DBCC SQLPERF (LOGSPACE).
--------------------------------------------------------------------------------
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.
If the log of a published (replicated) database becomes full and absolutely must be truncated, unsubscribe
all subscriptions to the publications of that database. This allows you to truncate all distributed
transactions.
NO_TRUNCATE
Backs up the log without truncating it. This option also backs up the log if the database becomes damaged
or if the database is marked suspect or has not been recovered.
Remarks
Database or log backups can be appended to any disk or tape device, allowing you to keep a database and
its transaction logs all within one physical location.
SQL Server uses online backup to allow a database backup while the database is still in use. However, here
are some operations that are not allowed 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.
CREATE INDEX. This restriction applies to database backup, not log backup.
Nonlogged operations such as bulk load, SELECT INTO, WRITETEXT, and UPDATETEXT. The database option select
into/bulkcopy must be enabled for these operations to be nonlogged.
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 backups, even between different processor types, can be performed, providing the following
sort order and character set restrictions are met:
Usually, the sort order and character set of two servers must match for you to back up on one server and
restore on the other. The exception to the sort order and character set matching between two servers is if
both servers use a binary sort order. Then, the character sets can differ.
It is possible to perform a backup on a server with a binary sort order and restore the back up onto a
different server with a binary sort order. If the character sets are the same, all characters are
interpreted identically between the two servers. If the character sets are different, however, any
extended characters are interpreted differently on the two servers. Even though the stored binary value is
the same, extended characters are interpreted differently depending on the installed character set. For
example, the character A (ASCII value of 65) is interpreted the same for all character sets. If the
character stored is ÷ (ASCII value of 246) on the first server with a character set of code page 437
(U.S. English), the second server with a character set of code page ISO 8859-1 (Latin 1 or ANSI),
interprets the ÷ character as the ö character (ASCII value still 246). For more information, see
Character Set.
Backup File Format
SQL Server backups can coexist on tape media with Windows NT backups because the SQL Server version 7.0
backup format conforms to Microsoft Tape Format (MTF), which is the same format used by Windows NT tape
backups.
Backup Types
Here are the types of backups SQL Server supports:
Full database backup, which backs up the entire database including the transaction log.
Differential database backup, which is performed between full database backups.
Transaction log backup.
Use BACKUP LOG when changes made since either the last differential database backup or last full database
backup are important to maintain.
File(s) and Filegroup(s) backup.
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 file backup 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 with FORMAT, the media
header field corresponding to the blank item is empty.
Use BACKUP to back up database files and filegroups instead of the full database when time constraints
make a full database backup impractical. If you choose to back up a file instead of the full database, put
procedures in place to assure 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 contents of the file forward to make it consistent with the rest of the database.
SQL Server must be able to read and write the device. Therefore, ownership and permission problems on the
backup device’s physical file can interfere with a backup operation. sp_addumpdevice, which adds an entry
for a device in the system tables, does not check file access permission. In fact, the physical resource
is not accessed until a backup or restore in attempted. Any backup sets with passwords will not be able to
be accessed.
You cannot execute the BACKUP LOG statement while the trunc. log on chkpt. database option is enabled, nor
can you execute it after enabling select into/bulkcopy and making unlogged changes to the database with
SELECT INTO, UPDATETEXT, WRITETEXT, or a bulk-copy operation. Use BACKUP DATABASE instead.
When a RESTORE is performed, the backup history tables are modified.
--------------------------------------------------------------------------------
Caution VOLUME is not an available option in SQL Server version 7.0. If VOLUME is specified, SQL Server
returns an error message and halts the operation.
--------------------------------------------------------------------------------
Interaction of SKIP, NOSKIP, INIT, and NOINIT
This table shows how the {INIT | NOINIT} and {NOSKIP | SKIP} clauses interact.
INIT NOINIT
SKIP If the volume contains a valid** media header, verify the media password* and overwrite any backup
sets on the media, preserving only the media header.
If the tape media is empty or the disk media is nonexistent, write a media header and proceed. If the
media is not empty, however, give feedback that this is not valid MTF media and abort the backup. 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.
If the tape media is empty or the disk media is nonexistent, write a media header and proceed. If the
media is not empty, however, give feedback that this is not valid MTF media and cancel the backup.
NOSKIP If the volume contains a valid media header, perform the following checks:
Verify the media password***.
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 tape media is empty or the disk media is nonexistent, write a media header and proceed. If the
media is not empty, however, give feedback that this is not valid MTF media and cancel the backup. 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.
* This capability is not supported by SQL Server version 7.0.
** Validity includes the MTF version number and other header information. If the version specified is
unsupported or an unexpected value, an error occurs.
*** The presence of a media password prevents the backup operation. To override the backup operation,
specify FORMAT.
--------------------------------------------------------------------------------
Important 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
Permissions
BACKUP DATABASE and BACKUP LOG permissions default to members of the db_owner fixed database role, who can
transfer permissions to other users, and to members of the db_backupoperator fixed database role.
Examples
A. Back up the entire MyNwind database
--------------------------------------------------------------------------------
Note The MyNwind database is shown for illustration only.
--------------------------------------------------------------------------------
This example creates a backup device called MyNwind_1 that contains the full database backup of the
MyNwind database.
-- Create the backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1', 'c:\mssql7\backup\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
B. Back up the entire database and log
This example creates both a full database and log backup. The database is backed up to a backup device
called MyNwind_2, and then the log is backed up to a file called MyNwindLog1.
-- Create the backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_2', 'c:\mssql7\backup\MyNwind_2.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_2
-- Create the log backup device.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwindLog1',
'c:\mssql7\backup\MyNwindLog1.dat'
-- Update activity has occurred before this point.
-- Back up the log of the MyNwind database.
BACKUP LOG MyNwind
TO MyNwindLog1
__________________
|