【www.350.vip】数据库还原

数据库还原的操作,分两步实行:第一步,验证(verify卡塔尔(قطر‎备份文件;第二步,依照备份计策还原数据库;

参考《backup1:先导数据库备份》,备份计策是:

  • 一周三次完整备份,一天一回差别备份,风度翩翩钟头三遍事情日志备份
  • 数据/日志的历次备份都利用叁个单独的备份文件,数据备份的扩展名是
    .bak,日志备份的扩展名是.trn

生龙活虎,验证(Verifiy)备份文件

1,查看备份文件的公文列表(Data File 和 Log File卡塔尔(英语:State of Qatar)

是因为,数据或日志的历次备份,都利用三个单身的备份文件,由此,在备份文件中,独有一个backup
set,File选项是1,借使不点名该File选项,默许值是1。

RESTORE FILELISTONLY 
FROM disk = 'D:TestDBBackupFolderSitedb_bak4.bak'
--with file=1;

在SQL Server中,二个备份文件可以积累两个backup set,每三个backup
set都以数额或日志的二遍备份(完整或差别备份),这代表,三个备份文件能够存款和储蓄多少个数据库备份。为了便于管理备份文件,提议,每个备份都存款和储蓄到独门的备份文件中,那样,各个备份文件只存款和储蓄一遍备份。

重回的结果集中,有多少个非常关键的字段:

  • LogicalName:文件的逻辑名称
  • PhysicalName:文件的概略名称,是文件在OS上的路线+文件名,举例,D:Program
    FilesMicrosoft SQL ServerMSSQLDataSitedB.mdf;
  • Type:文件的种类(L:Log
    File,D:Data File,F:Full Text Catalog);

选项:FILE = backup_set_file_number,标志被苏醒的backup
set。

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.

2,验证(Verify)备份文件

运用Restore VerifyOnly
命令来评释备份文件的卓有成效,如若备份是可行的,SQL
Serer再次回到验证成功的音信。

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 }] [;]  

假若证实通过,SQL
Server会打字与印刷备份有效的音讯:

The backup set on file 1 is valid.

慎选
Move-To:用于评释磁盘是不是有丰富的Free Space来积累还原的数据库文件(Data
Files 和 Log Files);

Move子句钦点文件的LogicalName,To子句钦定文件的PhysicalName,即,存款和储蓄该公文的Path+FileName,比如:

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'

暗中认可景况下,在还原时,数据备份和日志备份将还原到原始的岗位(Original
Location),要是安排将数据库复制到别的Server上,使用Move-To选项是那几个须要的,在施行还原操作前,使用Restore
VerifyOnly命令,检查是有有足够的Disk Space,是或不是有神秘的文本命名矛盾。

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.

二,还原数据文件

运用restore dabase 命令将积累在备份文件中的 backup
还原成一个数据库,依照备份的不等,将数据库的余烬复起操作分为两片段:还原数据文件和死灰复然日志文件。

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

--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 ]

1,还原选项(RECOVEENCOREY | NORECOVECR-VY ),暗许值是RECOVELANDY

RECOVERY
选项:
钦命还原操作将富有未提交的业务回滚,并使数据库可用;假如后续须求从间距备份和业务日志备份继续苏醒操作,那么必得运用
NORECOVEHavalY选项;RECOVE本田CR-VY 选项用于恢复生机操作的终极一个Restore命令中。

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.

NORECOVERY 选项:内定还原操作不回滚未提交的事体,后续要求从出入备份或业务日志备份继续回涨操作,在还原经过的终极一个Restore命令在此之前,使用NoRecovery选项。

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.

2,移动选项(Move),仅用于恢复生机数据库完整备份

MOVE ‘**logical_file_name_in_backup’
TO ‘operating_system_file_name’
[ …
n ]   **                 

在还原经过中,将数据或日志文件移动到新的职位上,暗中认可景况下,数据库的各样文件,都会还原到原始的岗位上(Original
Location);要是须要改动数据库文件存款和储蓄的门路,通过move-to
选项,为数据库的各个文件钦点新的Location。

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.

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. 

3,替换选项(Replace),提出不要选拔Replace选项,仅用于复苏数据库完整备份**

在SQL
Server实例中,要是要还原的数量和现成的数据库同名,那么,钦赐Replace选项,SQL
Server将会把已存在的同名数据库删除。若无一点名Replace选项,SQL
Server会做安检,不会将现成的同名数据库删除。

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:

  • 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.

4,重启选项(Restart)


在数据库还原操作停顿(interrupt)时,重启还原操作,从暂停处重新开首回涨操作。

5,节制顾客(RESTEvoqueICTED_USER),仅用于复苏数据库完整备份**

对新还原的数据库,节制(restrict)客商访谈,只允许剧中人物 db_ownerdbcreator
或 sysadmin
 的成员的探访;

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

要施行职业日志的备份,数据库的复苏方式(Recovery
Mode)必得是FULL,而且数据库必需进行过贰次完整备份;不然,事务日志文件处于活动截断(Auto-Truncate)状态,不能实践工作日志的备份。

www.350.vip 1www.350.vip 2

--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'] 
   } 

View Code

在还原事情日志时,SQL Server扶持复苏届期间点,在Restore
Log命令中钦定StopAt选项,能够将工作日志还原到实际的时间点。

四,还原数据文件示例

1,依次还原数据库的欧洲经济共同体备份,差别备份和事务日志备份

--完整备份还原
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;

2,将工作日志还原到某八个时刻点

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

五,还原发生的可怜等待 PARALLEL_BACKUP_QUEUE

进行 Restore HeaderOnly
命令的对话(Session)长日子处在PARALLEL_BACKUP_QUEUE
等待状态,也不可能Kill。

RESTORE HEADERONLY
from disk ='\xxxyyy.bak'

合丹麦语档:PARALLEL_BACKUP_QUEUE occurs
when serializing output produced by RESTORE HEADERONLY, RESTORE
FILELISTONLY, or RESTORE LABELONLY.

PARALLEL_BACKUP_QUEUE
等待表示,在行使并发/多进度复苏数据库的历程中,二个经过在伺机访谈输出结果集。那个结果集被现身的有所进度分享,由此,在三个进度写入新的数目早前,结果聚焦的数码必须联合。正是说,还原数据库的八个经过必须以连串化的艺术出口结果集。

出现PARALLEL_BACKUP_QUEUE等待的原故是命令RESTORE HEADERONLY
会将围观备份媒介(backup media)上的持有备份集(backup
set),三个备份媒介恐怕存款和储蓄多少个备份集,扫描全数的备份集特别耗费时间。提出利用RESTORE
LABELONLY,该命令只会读取备份媒介的头顶(header)音信。

如图,只读取Meida Header,能够相当的慢获得backup meida富含的音信,可是Header
包罗的新闻没有多少于。

www.350.vip 3

 

参照文书档案:

RESTORE
(Transact-SQL).aspx)

RESTORE VERIFYONLY
(Transact-SQL).aspx)

RESTORE FILELISTONLY
(Transact-SQL).aspx)

发表评论

电子邮件地址不会被公开。 必填项已用*标注