[SQL Server]数据库的恢复

图片 1

数据库恢复是和数据库备份相对应的操作,它是将数据库备份重新加载到系统中的过程。数据库恢复可以创建备份完成时数据库中存在的相关文件,但是备份以后的所有数据库修改都将丢失。

–完整还原

 

备份 (Transact SQL) | Microsoft Docs
   

   SQL
Server进行数据库恢复时,系统将自动进行安全性检查,以防止误操作而使用了不完整的信息或其他的数据备份覆盖现有的数据库。当出现以下几种情况时,系统将不能恢复数据库。

RESTORE DATABASE demoData
   FROM DISK=N'D:BackupdemoData.bak';  

 

  (1)还原操作中的数据库名称与备份集中记录的数据库名称不匹配。

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

 

还原 (Transact SQL) | Microsoft Docs
  

  (2)需要通过还原操作自动创建一个或多个文件,但已有同名的文件存在。

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

 

 
(3)还原操作中命名的数据库已在服务器上,但是与数据库备份中包含的数据库不是同一个数据库,例如数据库名称虽相同,但是数据库的创建方式不同。

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

恢复模式

SQL
Server 备份和还原操作发生在数据库的恢复模式的上下文中。 恢复模式旨在控制事务日志维护。 “恢复模式”是一种数据库属性,它控制如何记录事务,事务日志是否需要(以及允许)进行备份,以及可以使用哪些类型的还原操作。 有三种恢复模式:简单恢复模式、完整恢复模式和大容量日志恢复模式。 通常,数据库使用完整恢复模式或简单恢复模式。 数据库可以随时切换为其他恢复模式。

 

   如果重新创建一个数据库,可以禁止这些安全检查。

–恢复数据库的一部分(部分还原)
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 ]
[;]

备份

一。数据库恢复模型

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

完整备份

 完整数据库备份可对整个数据库进行备份。 这包括对部分事务日志进行备份,以便在还原完整数据库备份之后,能够恢复完整数据库备份。 完整数据库备份表示备份完成时的数据库。

根据保存数据的需要和对存储介质使用的考虑,SQL
Server提供了3种数据库恢复模型:简单恢复、完全恢复、大容量日志记录恢复。

–将特定页面还原到数据库(页面还原) 
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 ] ]
[;]

例如 ( Transact-SQL)

下面的示例说明了如何使用 WITH FORMAT
覆盖任意现有备份并创建新介质集,从而创建一个完整数据库备份。 然后,此示例将备份事务日志。 在现实情况下,您必须执行一系列的定期日志备份。 在此示例中, AdventureWorks2012 示例数据库设置为使用完整恢复模式。

USE master;
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO
— Back up the AdventureWorks2012 database to new media set (backup set
1).

BACKUP DATABASE AdventureWorks2012
TO DISK = ‘Z:SQLServerBackupsAdventureWorks2012FullRM.bak’
WITH FORMAT;
GO
–Create a routine log backup (backup set 2).
BACKUP LOG AdventureWorks2012 TO DISK =
‘Z:SQLServerBackupsAdventureWorks2012FullRM.bak’;

GO

* *

1.简单恢复模型

–将事务日志还原到数据库(事务日志还原)
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 ]
]
[;]

差异备份

差异备份所基于的是最近一次的完整数据备份。 差异备份仅捕获自该次完整备份后发生更改的数据。 差异备份所基于的完整备份称为差异的“基准”
。 完整备份(仅复制备份除外)可以用作一系列差异备份的基准,包括数据库备份、部分备份和文件备份。 文件差异备份的基准备份可以包含在完整备份、文件备份或部分备份中。

优点

  • 与创建完整备份相比,创建差异备份的速度可能非常快。 差异备份只记录自差异备份所基于的完整备份后更改的数据。 这有助于频繁地进行数据备份,减少数据丢失的风险。 但是,在还原差异备份之前,必须先还原其基准。 因此,从差异备份进行还原必然要比从完整备份进行还原需要更多的步骤和时间,因为这需要两个备份文件。

  • 如果数据库的某个子集比该数据库的其余部分修改得更为频繁,则差异数据库备份特别有用。 在这些情况下,使用差异数据库备份,您可以频繁执行备份,并且不会产生完整数据库备份的开销。

  • 在完整恢复模式下,使用差异备份可以减少必须还原的日志备份的数量。

 

 

 

 
简单恢复模型可以将数据库恢复到上次备份处,但是无法将数据库还原到故障点或待定的即时点。它常用于恢复最新的完整数据库备份、差异备份。

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

日志备份

 

 

 

 简单恢复模型的有点是允许高性能大容量复制操作,以及可以回收日志空间。但是必须重组最新的数据库或者差异备份后的更改。

 

仅复制备份

 仅复制备份
是独立于常规 SQL Server 备份序列的 SQL Server 备份。 通常,进行备份会更改数据库并影响其后备份的还原方式。 但是,有时在不影响数据库总体备份和还原过程的情况下,为特殊目的而进行备份还是有用的。 仅复制备份就是用于此目的。

 

 

2.完全恢复模型

 

 

 
完全恢复模型使用数据库备份和事务日志备份提供将数据库恢复到故障点或特定即时点的能力。为保证这种恢复程度,包括大容量操作(如SELECT
INTO、CREATE INDEX和大容量装载数据)在内的所有操作都将完整地记入日志。

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

恢复

完全恢复模型的优点是可以恢复到任意即时点,这样数据文件的丢失和损坏不会导致工作损失,但是如果事务日志损坏,则必须重新做最新的日志备份后进行修改。

<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 创建的备份称为“部分备份”。在简单恢复模式下,只允许对只读文件组执行文件组备份。还原的数据备份类型:数据库备份、部分备份或文件备份。对于数据库备份或部分备份,日志备份序列必须从数据库备份或部分备份的结尾处开始延续。对于一组文件备份,日志备份序列必须从整组文件备份的开头开始延续。

完整数据库还原(简单恢复模式) 

 

数据库完整还原的目的是还原整个数据库。 整个数据库在还原期间处于脱机状态。 在数据库的任何部分变为联机之前,必须将所有数据恢复到同一点,即数据库的所有部分都处于同一时间点并且不存在未提交的事务。

在简单恢复模式下,数据库不能还原到特定备份中的特定时间点。

用于还原完整数据库备份的基本 Transact-SQLRESTORE 语法是:

RESTORE
DATABASE database_name FROM backup_device [ WITH NORECOVERY
]

示例
(Transact-SQL)

以下示例首先显示如何使用 BACKUP 语句来创建 AdventureWorks2012 数据库的完整数据库备份和差异数据库备份。 然后按顺序还原这些备份。 将数据库还原到完成差异数据库备份时的状态。

该示例说明数据库完整还原方案的还原序列中的关键选项。 还原顺序 由通过一个或多个还原阶段来移动数据的一个或多个还原操作组成。 将省略与此目的不相关的语法和详细信息。 在恢复数据库时,尽管 RECOVERY
选项是默认值,但为清楚起见,仍建议显式指定该选项。

USE master;
–Make sure the database is using the simple recovery model.
ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE;
GO
— Back up the full AdventureWorks2012 database.
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘Z:SQLServerBackupsAdventureWorks2012.bak’
WITH FORMAT;
GO
–Create a differential database backup.
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘Z:SQLServerBackupsAdventureWorks2012.bak’
WITH DIFFERENTIAL;
GO
–Restore the full database backup (from backup set 1).
RESTORE DATABASE AdventureWorks2012
FROM DISK = ‘Z:SQLServerBackupsAdventureWorks2012.bak’
WITH FILE=1, NORECOVERY;
–Restore the differential backup (from backup set 2).
RESTORE DATABASE AdventureWorks2012
FROM DISK = ‘Z:SQLServerBackupsAdventureWorks2012.bak’
WITH FILE=2, RECOVERY;
GO

 

 

 

 

 

3.大容量日志记录恢复模型

 }

完整数据库还原(完整恢复模式)

将数据库还原到故障点

 

通常,将数据库恢复到故障点分为下列基本步骤:

  1. 备份活动事务日志(称为日志尾部)。 此操作将创建结尾日志备份。 如果活动事务日志不可用,则该日志部分的所有事务都将丢失。

    重要

    在大容量日志恢复模式下,备份任何包含大容量日志操作的日志都需要访问数据库中的所有数据文件。 如果无法访问该数据文件,则不能备份事务日志。 在这种情况下,您必须手动重做自最近备份日志以来所做的所有更改。

    有关详细信息,请参阅结尾日志备份 (SQL
    Server)。

  2. 还原最新完整数据库备份而不恢复数据库
    (RESTORE DATABASE database_name FROM backup_device WITH
    NORECOVERY)。

  3. 如果存在差异备份,则还原最新的差异备份而不恢复数据库
    (RESTORE
    DATABASE database_name FROM differential_backup_device WITH
    NORECOVERY).。

    还原最新差异备份可减少必须还原的日志备份数。

  4. 从还原备份后创建的第一个事务日志备份开始,使用
    NORECOVERY 依次还原日志。

  5. 恢复数据库
    (RESTORE DATABASE database_name WITH RECOVERY)。 此步骤也可以与还原上一次日志备份结合使用。

    下图说明此还原顺序。 故障发生后 (1),将创建结尾日志备份
    (2)。 接着,将数据库还原到该故障点。 这涉及到还原数据库备份、后续差异备份以及在差异备份后执行的每个日志备份,包括结尾日志备份。

    图片 1

 
大容量日志记录恢复模型为某些大规模或大容量复制操作提供最佳性能和最少日志使用空间。在这种模型中,大容量复制操作的数据丢失程度要比完全恢复模型严重,因为在这种模型下,只记录操作的最小日志,无法逐个控制这些操作。它只允许数据库恢复到事务日志备份的结尾处,不支持即时点恢复。

<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 的成员的访问

基本 TRANSACT-SQL RESTORE 语法

上图中还原顺序的基本 RESTORE Transact-SQL 语法如下:

  1. RESTORE DATABASE database FROM full
    database backup
     WITH NORECOVERY;

  2. RESTORE
    DATABASE database FROM full_differential_backup WITH
    NORECOVERY;

  3. RESTORE
    LOG database FROM log_backup WITH NORECOVERY;

    对于其他每个日志备份,重复此还原日志步骤。

  4. RESTORE DATABASE database WITH
    RECOVERY;

 

 

示例:恢复到故障点 (Transact-SQL)

以下 Transact-SQL 示例显示了将数据库还原到故障点的还原顺序中的基本选项。 此示例将创建数据库的结尾日志备份。 接下来,此示例将还原完整数据库备份和日志备份,然后还原结尾日志备份。 此示例将在最后的单独步骤中恢复数据库。

USE master;
–Create tail-log backup.
BACKUP LOG AdventureWorks2012
TO DISK = ‘Z:SQLServerBackupsAdventureWorksFullRM.bak’
WITH NORECOVERY;
GO
–Restore the full database backup (from backup set 1).
RESTORE DATABASE AdventureWorks2012
FROM DISK = ‘Z:SQLServerBackupsAdventureWorksFullRM.bak’
WITH FILE=1,
NORECOVERY;

–Restore the regular log backup (from backup set 2).
RESTORE LOG AdventureWorks2012
FROM DISK = ‘Z:SQLServerBackupsAdventureWorksFullRM.bak’
WITH FILE=2,
NORECOVERY;

–Restore the tail-log backup (from backup set 3).
RESTORE LOG AdventureWorks2012
FROM DISK = ‘Z:SQLServerBackupsAdventureWorksFullRM.bak’
WITH FILE=3,
NORECOVERY;
GO
–recover the database:
RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
GO

 

 

 
大容量日志记录恢复模型的优点是可以节省日志空间,但是如果日志损坏或者日志备份后发生了大容量操作,则必须重做自上次备份后所做的更改。

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

将数据库还原到指定时间点

 

在完整恢复模式下,完整的数据库还原通常可恢复到日志备份中的某个时间点、标记的事务或
LSN。 但是,在大容量日志恢复模式下,如果日志备份包含大容量更改,则不能进行时点恢复。

时点还原方案示例

下例假定针对一个关键任务型数据库系统,每天午夜创建一个完整数据库备份;从星期一到星期六,每小时创建一个差异数据库备份;全天每
10 分钟创建一个事务日志备份。 若要将数据库还原到星期三凌晨 5:19 的状态, 请执行以下操作:

  1. 还原星期二午夜创建的完整数据库备份。

  2. 还原星期四凌晨 5:00
    创建的差异数据库 备份。

  3. 应用星期四凌晨
    5:10创建的事务日志 备份。

  4. 应用星期三凌晨 5:20
    创建的事务日志 备份,指定恢复进程仅应用到凌晨 5:19
    之前发生的事务。

    或者,如果需要将数据库还原到它在星期四凌晨
    3:04 的状态, 而在星期四凌晨 3:00
    创建的差异数据库备份已不可用, 则执行下列操作:

  5. 还原在星期三午夜创建的数据库备份。

  6. 还原星期四凌晨 2:00
    创建的差异数据库 备份。

  7. 应用从星期四凌晨 2:10 到 3:00
    创建的所有事务 日志 备份。

  8. 应用星期四凌晨 3:10
    创建的事务日志 备份,停止凌晨 3:04
    的恢复进程。

 

 不同的hi付模型针对不同的性能、磁盘和磁带空间以及保护数据丢失的需要。恢复模型决定总体备份策略,包括可以使用的备份类型,即选择一种恢复模型,可以确定如何备份数据以及能承受何种程度的数据丢失,由此确定了数据的恢复过程。

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

二。查看备份信息

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

由于恢复数据库与备份数据库之间往往存在较长的时间差,难以记住备份设备和备份文件及其所备份的数据库,需要对这些信息进行查看。

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

 
需要查看的信息通常包括:备份集内的数据和日志文件、备份首部信息、介质首部信息。可以使用SQL
Server管理平台和Transact-SQL语句查看这些信息。

–检测选项 
| STATS [ = percentage ]

 1.使用SQL Server管理平台查看备份信息

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

  使用SQL Server查看所有备份介质属性的操作步骤如下:

<replication_WITH_option>::=
| KEEP_REPLICATION

  (1) 打开SQL
Server管理平台,在对象资源管理器中,展开结点”服务器树“→”服务器对象“→”备份设备“,右击某个具体备份设备名称,在弹出的快捷菜单上选择”属性“命令,打开”备份设备“属性窗口。

<change_data_capture_WITH_option>::=
| KEEP_CDC

(2)在”备份设备“属性窗口选择”媒体内容“选择卡,打开的窗口,在列表框中列出所选备份媒体的有关信息。

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

2.使用Transact-SQL语句查看备份信息

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

 RESTORE HEADERONLY语句的格式为:

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

RESTORE HEADERONLY

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

FROM <backpi_device>

[WITH {NOUNLOAD|UNLOAD}

[[,]FILE =file_number]

[[,]PASSWORD={password|@password_var}]

[[,]MEDIAPASSWORD={mediapassword|@mediapassword_var}]

<backup_device>::={

{‘logical_backup_device_name’|@logical_backup_device_name_var}

|{DISK|TAPE}={‘physical_backup_device_name’|@physical_backup_name_var}

}]

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图