第1个回答 2005-12-14
BACKUP
备份整个数据库、事务日志,或者备份一个或多个文件或文件组。有关数据库备份和恢复操作的更多信息,请参见备份和还原数据库。
语法
备份整个数据库:
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 ] ]
]
备份特定的文件或文件组:
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 ] ]
]
备份一个事务日志:
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 }
}
截断事务日志:
BACKUP LOG { database_name | @database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
参数
DATABASE
指定一个完整的数据库备份。假如指定了一个文件和文件组的列表,那么仅有这些被指定的文件和文件组被备份。
说明 在进行完整数据库备份或差异数据库备份时,Microsoft® SQL Server™ 备份足够的事务日志,以生成一个将在还原数据库时使用的一致的数据库。在 master 数据库上只能采用完整数据库备份。
{ database_name | @database_name_var }
指定了一个数据库,从该数据库中对事务日志、部分数据库或完整的数据库进行备份。如果作为变量 (@database_name_var) 提供,则可将该名称指定为字符串常量 (@database_name_var = database name) 或字符串数据类型(ntext 或 text 数据类型除外)的变量。
< backup_device >
指定备份操作时要使用的逻辑或物理备份设备。可以是下列一种或多种形式:
{ logical_backup_device_name } | { @logical_backup_device_name_var }
是由 sp_addumpdevice 创建的备份设备的逻辑名称,数据库将备份到该设备中,其名称必须遵守标识符规则。如果将其作为变量 (@logical_backup_device_name_var) 提供,则可将该备份设备名称指定为字符串常量 (@logical_backup_device_name_var = logical backup device name) 或字符串数据类型(ntext 或 text 数据类型除外)的变量。
{ DISK | TAPE } =
'physical_backup_device_name' | @physical_backup_device_name_var
允许在指定的磁盘或磁带设备上创建备份。在执行 BACKUP 语句之前不必存在指定的物理设备。如果存在物理设备且 BACKUP 语句中没有指定 INIT 选项,则备份将追加到该设备。
当指定 TO DISK 或 TO TAPE 时,请输入完整路径和文件名。例如,DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mybackup.dat' 或 TAPE = '\\.\TAPE0'。
说明 对于备份到磁盘的情况,如果输入一个相对路径名,备份文件将存储到默认的备份目录中。该目录在安装时被设置并且存储在 KEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer 目录下的 BackupDirectory 注册表键值中。
如果使用的是具有统一命名规则 (UNC) 名称的网络服务器或已重新定向的驱动器号,则请指定磁盘的设备类型。
当指定多个文件时,可以混合逻辑文件名(或变量)和物理文件名(或变量)。但是,所有的设备都必须为同一类型(磁盘、磁带或管道)。
Windows 98 不支持备份到磁盘。
RESTORE
还原使用 BACKUP 命令所做的备份。有关数据库备份和还原操作的更多信息,请参见备份和还原数据库。
语法
还原整个数据库:
RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
还原数据库的部分内容:
RESTORE DATABASE { database_name | @database_name_var }
< file_or_filegroup > [ ,...n ]
[ FROM < backup_device > [ ,...n ] ]
[ WITH
{ PARTIAL }
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] NORECOVERY ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
还原特定的文件或文件组:
RESTORE DATABASE { database_name | @database_name_var }
< file_or_filegroup > [ ,...n ]
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] NORECOVERY ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
还原事务日志:
RESTORE LOG { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [= percentage ] ]
[ [ , ] STOPAT = { date_time | @date_time_var }
| [ , ] STOPATMARK = 'mark_name' [ AFTER datetime ]
| [ , ] STOPBEFOREMARK = 'mark_name' [ AFTER datetime ]
]
]
< 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}
}
参数
DATABASE
指定从备份还原整个数据库。如果指定了文件和文件组列表,则只还原那些文件和文件组。
{database_name | @database_name_var}
是将日志或整个数据库还原到的数据库。如果将其作为变量 (@database_name_var) 提供,则可将该名称指定为字符串常量 (@database_name_var = database name) 或字符串数据类型(ntext 或 text 数据类型除外)的变量。
FROM
指定从中还原备份的备份设备。如果没有指定 FROM 子句,则不会发生备份还原,而是恢复数据库。可用省略 FROM 子句的办法尝试恢复通过 NORECOVERY 选项还原的数据库,或切换到一台备用服务器上。如果省略 FROM 子句,则必须指定 NORECOVERY、RECOVERY 或 STANDBY。
< backup_device >
指定还原操作要使用的逻辑或物理备份设备。可以是下列一种或多种形式:
{'logical_backup_device_name' | @logical_backup_device_name_var}
是由 sp_addumpdevice 创建的备份设备(数据库将从该备份设备还原)的逻辑名称,该名称必须符合标识符规则。如果作为变量 (@logical_backup_device_name_var) 提供,则可以指定字符串常量 (@logical_backup_device_name_var = logical_backup_device_name) 或字符串数据类型(ntext 或 text 数据类型除外)的变量作为备份设备名。
{DISK | TAPE } =
'physical_backup_device_name' | @physical_backup_device_name_var
允许从命名磁盘或磁带设备还原备份。磁盘或磁带的设备类型应该用设备的真实名称(例如:完整的路径和文件名)来指定:DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mybackup.dat' 或 TAPE = '\\.\TAPE0'。如果指定为变量 (@physical_backup_device_name_var),则设备名称可以是字符串常量 (@physical_backup_device_name_var = 'physical_backup_device_name') 或字符串数据类型(ntext 或 text 数据类型除外)的变量。
如果使用的是含有 UNC 名称的网络服务器或已重新定向的驱动器号,则请指定磁盘的设备类型。运行 SQL Server 时所使用的帐户必须具有对远程计算机或网络服务器的 READ 访问权,这样才能执行RESTORE 操作。