xml地图|网站地图|网站标签 [设为首页] [加入收藏]
当前位置: www8029com > 澳门新葡8522最新网站 > 正文

澳门新葡8522最新网站数据库还原

时间:2019-09-12 06:18来源:澳门新葡8522最新网站
数据库还原的操作,分两步进行:第一步,验证(verify)备份文件;第二步,依据备份计谋还原数据库; 参考《backup1:起始数据库备份》,备份攻略是: 七日一次完整备份,一天三回差

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

参考《backup1:起始数据库备份》,备份攻略是:

  • 七日一次完整备份,一天三回差别备份,半小时二次事情日志备份
  • 数据/日志的历次备份都施用一个独立的备份文件,数据备份的庞大名是 .bak,日志备份的扩大名是.trn

一,验证(Verifiy)备份文件

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

鉴于,数据或日志的历次备份,都使用三个独立的备份文件,由此,在备份文件中,唯有三个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,还原选项(RECOVE帕杰罗Y | NORECOVE路虎极光Y ),暗中同意值是RECOVECR-VY

RECOVERY 选项:钦点还原操作将有着未提交的政工回滚,并使数据库可用;假使持续须要从距离备份和工作日志备份继续上涨操作,那么必得运用 NORECOVE中华VY选项;RECOVE福特ExplorerY 选项用于苏醒操作的最后二个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,限制客户(RESTCR-VICTED_USER),仅用于苏醒数据库完整备份**

对新还原的数据库,限制(restrict)顾客访谈,只同意角色 db_ownerdbcreator 或 sysadmin 的成员的探问;

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

要奉行工作日志的备份,数据库的苏醒格局(Recovery Mode)必得是FULL,并且数据库必需开展过叁次完整备份;不然,事务日志文件处于活动截断(Auto-Truncate)状态,不恐怕实行工作日志的备份。

澳门新葡8522最新网站 1澳门新葡8522最新网站 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 富含的新闻十分的少于。

澳门新葡8522最新网站 3

 

参照文书档案:

RESTORE (Transact-SQL).aspx)

RESTORE VERIFYONLY (Transact-SQL).aspx)

RESTORE FILELISTONLY (Transact-SQL).aspx)

编辑:澳门新葡8522最新网站 本文来源:澳门新葡8522最新网站数据库还原

关键词: www8029com