Sqlserver 还原【上海时时乐走势图官网】

四,还原数据文件示例

 

在SQL Server中,一个备份文件可以存储多个backup set,每一个backup set都是数据或日志的一次备份(完整或差异备份),这意味着,一个备份文件能够存储多个数据库备份。为了便于管理备份文件,建议,每一个备份都存储到单独的备份文件中,这样,每个备份文件只存储一次备份。

 }

Move子句指定文件的LogicalName,To子句指定文件的PhysicalName,即,存储该文件的Path FileName,例如:

--恢复数据库的一部分(部分还原)
RESTORE DATABASE { database_name | @database_name_var }
<files_or_filegroups> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
PARTIAL, NORECOVERY
[ , <general_WITH_options> [ ,...n ]
| , <point_in_time_WITH_options—RESTORE_DATABASE>
] [ ,...n ]
[;]

出现PARALLEL_BACKUP_QUEUE等待的原因是命令RESTORE HEADERONLY 会将扫描备份媒介(backup media)上的所有备份集(backup set),一个备份媒介可能存储多个备份集,扫描所有的备份集十分耗时。建议使用RESTORE LABELONLY,该命令只会读取备份媒介的头部(header)信息。

--媒体设置选项 
| MEDIANAME = { media_name | @media_name_variable }
| MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
| BLOCKSIZE = { blocksize | @blocksize_variable }

选项 Move-To:用于验证磁盘是否有足够的Free Space来存储还原的数据库文件(Data Files 和 Log Files);

<service_broker_WITH_options>::=
| ENABLE_BROKER
| ERROR_BROKER_CONVERSATIONS
| NEW_BROKER

RESTORE FILELISTONLY (Transact-SQL).aspx)

<backup_device>::= --备份设备定义
{
{ logical_backup_device_name |
@logical_backup_device_name_var }
| { DISK | TAPE | URL } = { 'physical_backup_device_name' |  
@physical_backup_device_name_var }
}

3,替换选项(Replace),建议不要使用Replace选项,仅用于还原数据库完整备份**

<point_in_time_WITH_options—RESTORE_LOG>::=
| {
STOPAT = { 'datetime'| @datetime_var }
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime']
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime']
}

要执行事务日志的备份,数据库的恢复模式(Recovery Mode)必须是FULL,并且数据库必须进行过一次完整备份;否则,事务日志文件处于自动截断(Auto-Truncate)状态,无法执行事务日志的备份。

--错误检测选项 
| { CHECKSUM | NO_CHECKSUM }  --检测校验和
| { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }

--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE database_name  
FROM DISK  'physical_backup_device_name'
[ WITH 
  {[ RECOVERY | NORECOVERY  ]
   | , <general_WITH_options> [ ,...n ]} 
][;]
<general_WITH_options> ::=  
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' 
   [ ,...n ] 
 | REPLACE 
 | RESTART 
 | RESTRICTED_USER 
 | FILE = { backup_set_file_number | @backup_set_file_number } 
 | STATS [ = percentage ]

--将事务日志还原到数据库(事务日志还原)
RESTORE LOG { database_name | @database_name_var }
[ <file_or_filegroup_or_pages> [ ,...n ] ]
[ FROM <backup_device> [ ,...n ] ]
[ WITH
{
[ RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var }
]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <point_in_time_WITH_options—RESTORE_LOG>
} [ ,...n ]
]
[;]

MOVE '**logical_file_name_in_backup' TO 'operating_system_file_name' [ ...n ]   **                 

--数据事务选项 
| BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

在数据库还原操作中断(interrupt)时,重启还原操作,从中断处重新开始还原操作。

<FILESTREAM_WITH_option>::=
| FILESTREAM ( DIRECTORY_NAME = directory_name )

在还原事务日志时,SQL Server支持还原到时间点,在Restore Log命令中指定StopAt选项,能够将事务日志还原到具体的时间点。

        | NORECOVERY --指定还原操作不回滚未提交的事务,后续需要从差异备份或事务日志备份继续还原操作,在还原过程的最后一个Restore命令之前,使用NoRecovery选项。

一,验证(Verifiy)备份文件

--完整还原

RECOVERY  option instructs the restore operation to roll back any uncommitted transactions. After the recovery process, the database is ready for use. If subsequent RESTORE operations (RESTORE LOG, or RESTORE DATABASE from differential) are planned, NORECOVERY should be specified instead.

--将特定文件或文件组还原到数据库(文件还原) 
RESTORE DATABASE { database_name | @database_name_var }
<file_or_filegroup> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
{
[ RECOVERY | NORECOVERY ]
[ , <general_WITH_options> [ ,...n ] ]
} [ ,...n ]
[;]

使用Restore VerifyOnly 命令来验证备份文件的有效性,如果备份是有效的,SQL Serer返回验证成功的消息。

<files_or_filegroups>::= --文件或文件组定义
{
FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }
| FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
| READ_WRITE_FILEGROUPS   --通过指定READ_WRITE_FILEGROUPS 创建的备份称为“部分备份”。在简单恢复模式下,只允许对只读文件组执行文件组备份。还原的数据备份类型:数据库备份、部分备份或文件备份。对于数据库备份或部分备份,日志备份序列必须从数据库备份或部分备份的结尾处开始延续。对于一组文件备份,日志备份序列必须从整组文件备份的开头开始延续。

When the REPLACE option is not specified, a safety check occurs. This prevents overwriting a different database by accident. The safety check ensures that the RESTORE DATABASE statement does not restore the database to the current server if the following conditions both exist:

--磁带选项 
| { REWIND | NOREWIND }
| { UNLOAD | NOUNLOAD }

restore verifyonly
from disk = 'D:TestDBBackupFolderSitedb_bak4.bak'
with 
file=1
,move 'Site_TestDB_1' to 'D:TestDBBackupFolderSite_TestDB_1.mdf' 
,move 'Site_TestDB_2' to 'D:TestDBBackupFolderSite_TestDB_2.ndf';
,move 'site_TestDB_log' to 'D:TestDBBackupFloderSite_TestDB_log.ldf'

<change_data_capture_WITH_option>::=
| KEEP_CDC

2,将事务日志还原到某一个时间点

--将特定页面还原到数据库(页面还原) 
RESTORE DATABASE { database_name | @database_name_var }
PAGE = 'file:page [ ,...n ]'
[ , <file_or_filegroups> ] [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
NORECOVERY
[ , <general_WITH_options> [ ,...n ] ]
[;]

  • The database named in the RESTORE statement already exists on the current server, and

  • The database name is different from the database name recorded in the backup set.

--将数据库还原到数据库快照捕获的时间点
RESTORE DATABASE { database_name | @database_name_var }
FROM DATABASE_SNAPSHOT = database_snapshot_name

NORECOVERY 选项:指定还原操作不回滚未提交的事务,后续需要从差异备份或事务日志备份继续还原操作,在还原过程的最后一个Restore命令之前,使用NoRecovery选项。

<point_in_time_WITH_options—RESTORE_DATABASE>::=
| {
STOPAT = { 'datetime'| @datetime_var }
| STOPATMARK = 'lsn:lsn_number'
[ AFTER 'datetime']
| STOPBEFOREMARK = 'lsn:lsn_number'
[ AFTER 'datetime']
}

默认情况下,在还原时,数据备份和日志备份将还原到原始的位置(Original Location),如果计划将数据库复制到其他Server上,使用Move-To选项是非常必要的,在执行还原操作前,使用Restore VerifyOnly命令,检查是有有足够的Disk Space,是否有潜在的文件命名冲突。

 

4,重启选项(Restart)

RESTORE DATABASE demoData
   FROM DISK=N'D:BackupdemoData.bak';  
  • 一周一次完整备份,一天一次差异备份,一小时一次事务日志备份
  • 数据/日志的每次备份都使用一个单独的备份文件,数据备份的扩展名是 .bak,日志备份的扩展名是.trn

--备份设置选项 
| FILE = { backup_set_file_number | @backup_set_file_number }
| PASSWORD = { password | @password_variable }

2,验证(Verify)备份文件

        | STANDBY =   
{standby_file_name | @standby_file_name_var } --RECOVERY(默认值)表示在当前备份完成前滚后,应执行回滚
]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <change_data_capture_WITH_option>
| , <FILESTREAM_WITH_option>
| , <service_broker_WITH options>
| , <point_in_time_WITH_options—RESTORE_DATABASE>
} [ ,...n ]
]
[;]

如图,只读取Meida Header,能够快速获取backup meida包含的信息,但是Header 包含的信息十分有限。

<replication_WITH_option>::=
| KEEP_REPLICATION

上海时时乐走势图官网 1上海时时乐走势图官网 2

RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,...n ] ]
[ WITH
{
[       RECOVERY --指定还原操作将所有未提交的事务回滚,并使数据库可用;如果后续需要从差异备份和事务日志备份继续还原操作,那么必须使用 NORECOVERY选项;RECOVERY 选项用于还原操作的最后一个Restore命令中。

Specifies that the data or log file whose logical name is specified by logical_file_name_in_backup should be moved by restoring it to the location specified by operating_system_file_name. The logical file name of a data or log file in a backup set matches its logical name in the database when the backup set was created.

<general_WITH_options> [ ,...n ]::= --普通WITH选项定义
--还原操作选项 
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
[ ,...n ]
| REPLACE --在SQL Server实例中,如果要还原的数据和现存的数据库同名,那么,指定Replace选项,SQL Server将会把已存在的同名数据库删除。如果没有指定Replace选项,SQL Server会做安全检查,不会将现存的同名数据库删除
| RESTART --在数据库还原操作中断(interrupt)时,重启还原操作,从中断处重新开始还原操作
| RESTRICTED_USER | CREDENTIAL --对新还原的数据库,限制(restrict)用户访问,只允许角色 db_owner, dbcreator 或 sysadmin 的成员的访问

  • LogicalName:文件的逻辑名称
  • PhysicalName:文件的物理名称,是文件在OS上的路径 文件名,例如,D:Program FilesMicrosoft SQL ServerMSSQLDataSitedB.mdf;
  • Type:文件的类型(L:Log File,D:Data File,F:Full Text Catalog);

--检测选项 
| STATS [ = percentage ]

还原数据文件的命令,简化

RESTORE FILELISTONLY 
FROM disk = 'D:TestDBBackupFolderSitedb_bak4.bak'
--with file=1;
RESTORE VERIFYONLY
FROM DISK = 'physical_backup_device_name'
[ WITH { MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,...n ] 
| FILE = backup_set_file_number }] [;]  

上海时时乐走势图官网 3

在SQL Server实例中,如果要还原的数据和现存的数据库同名,那么,指定Replace选项,SQL Server将会把已存在的同名数据库删除。如果没有指定Replace选项,SQL Server会做安全检查,不会将现存的同名数据库删除。

如果验证通过,SQL Server会打印备份有效的消息:

二,还原数据文件

官方文档:PARALLEL_BACKUP_QUEUE occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.

RESTORE HEADERONLY
from disk ='\xxxyyy.bak'

由于,数据或日志的每次备份,都使用一个单独的备份文件,因此,在备份文件中,只有一个backup set,File选项是1,如果不指定该File选项,默认值是1。

If a RESTORE VERIFYONLY statement is used when you plan to relocate a database on the same server or copy it to a different server, the MOVE option might be necessary to verify that sufficient space is available in the target and to identify potential collisions with existing files.

参考《backup1:开始数据库备份》,备份策略是:

5,限制用户(RESTRICTED_USER),仅用于还原数据库完整备份**

The backup set on file 1 is valid.

View Code

返回的结果集中,有三个非常重要的字段:

选项:FILE Sqlserver 还原【上海时时乐走势图官网】。= backup_set_file_number,标识被还原的backup set。

执行 Restore HeaderOnly 命令的会话(Session)长时间处于PARALLEL_BACKUP_QUEUE 等待状态,也不能Kill。

RECOVERY 选项:指定还原操作将所有未提交的事务回滚,并使数据库可用;如果后续需要从差异备份和事务日志备份继续还原操作,那么必须使用 NORECOVERY选项;RECOVERY 选项用于还原操作的最后一个Restore命令中。

使用restore dabase 命令将存储在备份文件中的 backup 还原成一个数据库,根据备份的不同,将数据库的还原操作分为两部分:还原数据文件和还原日志文件。

在还原过程中,将数据或日志文件移动到新的位置上,默认情况下,数据库的每个文件,都会还原到原始的位置上(Original Location);如果需要改变数据库文件存储的路径,通过move-to 选项,为数据库的每个文件指定新的Location。

1,查看备份文件的文件列表(Data File 和 Log File)

Sqlserver 还原【上海时时乐走势图官网】。RESTORE VERIFYONLY (Transact-SQL).aspx)

1,还原选项(RECOVERY | NORECOVERY ),默认值是RECOVERY

 

三,还原数据库的日志文件

对新还原的数据库,限制(restrict)用户访问,只允许角色 db_ownerdbcreator 或 sysadmin 的成员的访问;

2,移动选项(Move),仅用于还原数据库完整备份

--日志备份还原到某一个时间点
RESTORE log AdventureWorks2012
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012_3.trn'
WITH FILE = 1,
STATS=5,
STOPAT='Apr 15, 2016 12:00 AM'
RECOVERY;

1,依次还原数据库的完整备份,差异备份和事务日志备份


五,还原产生的异常等待 PARALLEL_BACKUP_QUEUE

--To Restore a Transaction Log:
RESTORE LOG database_name 
 [ <file_or_filegroup_or_pages> [ ,...n ] ]
 [ FROM <backup_device> [ ,...n ] ] 
 [ WITH 
   { 
     [ RECOVERY | NORECOVERY  ]
    | ,  <general_WITH_options> [ ,...n ]
    | , <point_in_time_WITH_options—RESTORE_LOG> 
   } [ ,...n ]
 ] [;]

<point_in_time_WITH_options—RESTORE_LOG>::= 
 | {
   STOPAT = { 'datetime'| @datetime_var } 
 | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime'] 
 | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime'] 
   } 

For restoring a database backup and one or more transaction logs or whenever multiple RESTORE statements are necessary (for example, when restoring a full database backup followed by a differential database backup), RESTORE requires the WITH NORECOVERY option on all but the final RESTORE statement. A best practice is to use WITH NORECOVERY on ALL statements in a multi-step restore sequence until the desired recovery point is reached, and then to use a separate RESTORE WITH RECOVERY statement for recovery only.

PARALLEL_BACKUP_QUEUE 等待表示,在使用并发/多进程还原数据库的过程中,一个进程在等待访问输出结果集。这个结果集被并发的所有进程共享,因此,在一个进程写入新的数据之前,结果集中的数据必须同步。就是说,还原数据库的多个进程必须以序列化的方式输出结果集。

Specify a MOVE statement for every logical file you want to restore from the backup set to a new location. By default, the logical_file_name_in_backup file is restored to its original location. 

参考文档:

For example, a backup_set_file_number of 1 indicates the first backup set on the backup medium and a backup_set_file_number of 2 indicates the second backup set. When not specified, the default is 1, except for RESTORE HEADERONLY in which case all backup sets in the media set are processed.

数据库还原的操作,分两步进行:第一步,验证(verify)备份文件;第二步,根据备份策略还原数据库;

--完整备份还原
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012.bak'
WITH FILE = 1,
STATS=5,
MOVE 'AdventureWorks2012 TO' 'D:SQLServerAdventureWorks2012.mdf',
MOVE 'AdventureWorks2012_Log' TO 'D:SQLServerAdventureWorks2012_log.ldf',
NORECOVERY;
--差异备份还原
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012_2.bak'
WITH FILE = 1,
STATS=5,
NORECOVERY;
--日志备份还原
RESTORE log AdventureWorks2012
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012_3.trn'
WITH FILE = 1,
STATS=5,
RECOVERY;

RESTORE (Transact-SQL).aspx)

本文由上海时时乐走势图发布于上海时时乐走势图官网,转载请注明出处:Sqlserver 还原【上海时时乐走势图官网】

您可能还会对下面的文章感兴趣: