数据库还原,开始数据库备份

图片 1
数据库

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

建议:每一次数据备份,都存储在单个备份文件上

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

BACKUP
(Transact-SQL).aspx)

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

backup database [TestSite]
to disk = 'D:\TestDBBackupFolder\Sitedb_bak1.bak' --specify new backup file
with
compression,
format,
init,
skip,
stats=5

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.

5,备份进度(stats)

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

4,建议:每一次数据备份,都存储在单个备份文件上


The STATS option reports the percentage
complete as of the threshold for reporting the next interval. This is at
approximately the specified percentage; for example, with STATS=10, if
the amount completed is 40 percent, the option might display 43 percent.
For large backup sets, this is not a problem, because the percentage
complete moves very slowly between completed I/O calls.

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

如果数据库符合以下两种条件之一,那么Database就处于自动截断模式:

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

1,尾日志备份

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

  • Format
    选项:
    将备份文件格式化,默认选项是 NoFormat;
  • Init
    选项:
    初始化备份文件,Init选项不会初始化Media Header,只将backup
    set初始化,默认选项是NoInit,将备份存储到备份文件的末尾;

  • SKIP 选项:不做任何检查,不会检查Media
    Header是否有效,也不会检查backup
    set的有效期,默认选项是NoSkip;

4,重启选项(Restart)

差异备份由DIFFERENTIAL 关键字指定,只备份从上一次完整备份之后发生更新的数据,而不是备份整个数据库,通常情况下,差异备份比完整备份占用的空间更少。差异备份的参考基准是上一次完整备份,而,事务日志,只备份是从上一次差异备份之后产生的事务日志。因此,备份是有顺序的,如果存在以下备份序列:

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

还原的策略是:备份尾日志,使数据库处于Restoring状态,依次还原FullBackup1.bak,DifferentialBackup4.bak,LogBackup5.trn,尾日志,就能将数据库还原到一个合适的有效时间点。

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

Backs up the tail of the log and leaves
the database in the RESTORING state. NORECOVERY is useful when failing
over to a secondary database or when saving the tail of the log before a
RESTORE operation. To perform a
best-effort log backup that skips log truncation and then take the
database into the RESTORING state atomically, use the NO_TRUNCATE and
NORECOVERY options together.

restore verifyonly
from disk = 'D:\TestDBBackupFolder\Sitedb_bak4.bak'
with 
file=1
,move 'Site_TestDB_1' to 'D:\TestDBBackupFolder\Site_TestDB_1.mdf' 
,move 'Site_TestDB_2' to 'D:\TestDBBackupFolder\Site_TestDB_2.ndf';
,move 'site_TestDB_log' to 'D:\TestDBBackupFloder\Site_TestDB_log.ldf'
backup log [TestSite]
to disk = 'D:\TestDBBackupFolder\Sitedb_bak3.trn'
with
compression,
format,
init,
skip,
stats=5

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

NORECOVERY 选项,指定备份事务日志的尾部,并使数据库处于RESTORING状态

RESTORE FILELISTONLY
(Transact-SQL).aspx)

3,自动截断模式

View Code

由于硬盘空间有限,不可能保留过多的备份文件,将数据的每一次备份都存储在单个文件上,便于对备份文件进行管理(删除或归档)。

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

为了便于管理数据备份文件,推荐的做法是:

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

3,压缩数据{ COMPRESSION | NO_COMPRESSION }

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

1,数据库完整备份,没有指定Differential选项

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

使用stats选项,每当备份进行到一定的百分比时,SQL
Server显式进度消息,默认值是10,即,每完成10%,SQL
Server显式完成的进度消息,例如,设置stats=10,当备份进程完成30%时,SQL
Server会打印消息:30
percent processed.

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

在备份时,将数据压缩,由于压缩的备份较小,能够减少Disk Sapce和Disk
IO消耗,提高数据备份的速度,但是,备份文件的压缩和解压缩十分消耗CPU资源。

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

2,日志截断

--完整备份还原
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
WITH FILE = 1,
STATS=5,
MOVE 'AdventureWorks2012 TO' 'D:\SQLServer\AdventureWorks2012.mdf',
MOVE 'AdventureWorks2012_Log' TO 'D:\SQLServer\AdventureWorks2012_log.ldf',
NORECOVERY;
--差异备份还原
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012_2.bak'
WITH FILE = 1,
STATS=5,
NORECOVERY;
--日志备份还原
RESTORE log AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012_3.trn'
WITH FILE = 1,
STATS=5,
RECOVERY;

三,事务日志备份

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

每次备份都存储在新的备份上,搭配选项
Init、Skip、Format,将数据备份存储在新的备份文件上,这三个选项的含义是:

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

  • During a full or differential
    database backup, SQL Server backs up enough of the transaction log
    to produce a consistent database when the backup is restored.

  • Only a full database backup can be
    performed on the master database.

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:

2,只复制(COPY_ONLY )备份

RESTORE
(Transact-SQL).aspx)

使用backup
database命令创建数据库的数据文件的备份,backup database
命令语法(简化):

参考文档:

在执行完整备份和差异备份时,SQL
Server会备份足够的事务日志,用于将数据库还原到一致性的状态。对于master数据库,只能执行完整备份。

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

backup database [TestSite]
to disk = 'D:\TestDBBackupFolder\Sitedb_bak2.bak' --specify new backup file
with 
differential,
compression, 
format, 
init, 
skip, 
stats=5

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

2,备份尾日志,进而还原数据库

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.

  • backup
    set:是数据或日志的一次备份;
  • backup
    media:是备份存储的文件,分为两部分:media
    header和content,content是由一个或多个backup sets构成的;
  • 镜像备份:一次备份操作创建一个相同的备份,最多三个镜像备份;
  • backup
     family:多个备份设备和镜像备份构成backup family;
--日志备份还原到某一个时间点
RESTORE log AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012_3.trn'
WITH FILE = 1,
STATS=5,
STOPAT='Apr 15, 2016 12:00 AM'
RECOVERY;

四,事务日志备份

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. 

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

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

 

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

备份是有顺序的,使用COPY_ONLY选项不会影响备份的正常顺序,仅仅创建一个数据库的副本。

2,验证(Verify)备份文件

select db_name(database_id) as DBName,
    case when last_log_backup_lsn is null then 'Auto' else 'Manul' end as TruncateMode
from sys.database_recovery_status

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

正常情况下,数据库处于Online状态,在进行事务日志备份时,如果不指定 NO_TRUNCATE
选项,那么数据库将已备份的事务日志文件截断,避免事务日志过大,耗尽disk空间;如果指定 NO_TRUNCATE
选项,表示日志备份不会将事务日志文件截断,该选项一般在数据库处于异常状态时使用。

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

自动截断模式是指数据库引擎把处于可还原状态(recoverable)状态的事务日志自动截断,使日志文件能够重复使用,避免日志文件无限增长。如果事务日志不是自动截断模式,那么事务日志会保存到日志文件中,导致日志文件持续增长。只有做日志备份时,日志文件才会被截断;如果没有定期的日志备份,那么日志文件会持续地增长,直到耗尽磁盘的所有空间,因此,必须制定一个日志备份计划,把事务日志截断,才能使数据库的事务日志文件的大小保持在一个可以管理的水平上。

RESTORE FILELISTONLY 
FROM disk = 'D:\TestDBBackupFolder\Sitedb_bak4.bak'
--with file=1;
  1. FullBackup1.bak
  2. DifferentialBackup2.bak
  3. LogBackup3.trn
  4. DifferentialBackup4.bak
  5. LogBackup5.trn
  6. 出现错误

二,还原数据文件

  • 数据/日志的每次备份都使用一个单独的备份文件,数据备份的扩展名是
    .bak,日志备份的扩展名是.trn;
  • 合理命名每个备份文件,建议使用:database_name+date+time+(.bak/.trn),该命名方式,很容易识别备份的数据库和开始备份的时间;
  • 创建schedule,定时清理备份文件,避免备份文件耗尽磁盘空间;
The backup set on file 1 is valid.

2,数据库差异备份,指定Differential选项**

 

一,创建数据库的完整备份和差异备份

图片 1

使用backup log命令对事务日志进行备份,跟backup
database命令的差异是,不能使用differential选项,多了NoRecovery 和
NO_Truncate选项;

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

二,数据备份操作

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.

Specifies that the log not be truncated
and causes the Database Engine to attempt the backup regardless of the
state of the database. Consequently, a backup taken with NO_TRUNCATE
might have incomplete metadata. This option allows backing up the log in
situations where the database is damaged.
The NO_TRUNCATE option of BACKUP LOG is
equivalent to specifying both COPY_ONLY and
CONTINUE_AFTER_ERROR.
Without the
NO_TRUNCATE option, the database must be in the ONLINE state. If
the database is in the SUSPENDED state, you might be able to create a
backup by specifying NO_TRUNCATE. But if the database is in the OFFLINE
or EMERGENCY state, BACKUP is not allowed even with
NO_TRUNCATE. 

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

参考doc:

选项:FILE = backup_set_file_number,标识被还原的backup
set。

  • 数据库的恢复模式是simple;
  • 数据库的恢复模式是full 或 bulk_Logged,并且没有做过数据库完整备份;

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

1,完整备份和差异备份

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

BACKUP DATABASE database_name 
TO DISK  =  'physical_device_name'
[ WITH { DIFFERENTIAL
|  COPY_ONLY 
| { COMPRESSION | NO_COMPRESSION } 
| { NOINIT | INIT } 
| { NOSKIP | SKIP } 
| { NOFORMAT | FORMAT } 
| STATS [ = percentage ] }]

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

BACKUP LOG database_name 
TO DISK  =  'physical_device_name'
[ WITH { 
  COPY_ONLY
| { COMPRESSION | NO_COMPRESSION } 
| { NOINIT | INIT } 
| { NOSKIP | SKIP } 
| { NOFORMAT | FORMAT } 
| STATS [ = percentage ] 
| { NORECOVERY | STANDBY = undo_file_name }
| NO_TRUNCATE }]
--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 ]
backup log [TestSite]
to disk = 'D:\TestDBBackupFolder\Sitedb_bak4.trn'
with
compression,
format,
init,
skip,
stats=5,
norecovery

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

数据库备份的策略一般是:一周一次完整备份,一天一次差异备份,一小时一次事务日志备份,根据数据容灾的要求,适当增减备份的时间间隔。

一,验证(Verifiy)备份文件

数据库备份分为数据文件备份和日志文件备份,数据文件的备份分为:完整备份和差异备份。在SQL
Server
2012中,能够将数据分布式备份到不同的存储设备上,一般情况,只将数据备份到一个备份文件(.bak)中,只有在备份超大的数据库时,才需要分布式备份,对于备份集(backup
set),备份介质(backup Media),备份族(backup
family),镜像备份,等等看似复杂的术语,不用深入了解,简单了解一下基本知识:

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

差异备份的基准是上一次完整备份,即差异是指从上一次full
backup之后,对数据文件执行的更新操作。如果执行一次Copy-Only的完整数据库备份,不会影响差异备份的base(基准),该base是上一次full
backup,而非本次 Copy-only full backup。

四,还原数据文件示例

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

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

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.

  • 一周一次完整备份,一天一次差异备份,一小时一次事务日志备份
  • 数据/日志的每次备份都使用一个单独的备份文件,数据备份的扩展名是
    .bak,日志备份的扩展名是.trn

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

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

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

RESTORE HEADERONLY
from disk ='\\xxx\yyy.bak'

图片 2图片 3

RESTORE VERIFYONLY
(Transact-SQL).aspx)

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

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

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.

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

发表评论

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

网站地图xml地图