您的位置:新葡亰496net > 网络数据库 > SqlServer备份恢复生机,数据库还原

SqlServer备份恢复生机,数据库还原

发布时间:2019-08-02 13:04编辑:网络数据库浏览(176)

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

    --完整还原

     

    备份 (Transact SQL) | Microsoft Docs    

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

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

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

     

    • backup set:是数据或日志的一次备份;
    • backup media:是备份存储的文件,分为两部分:media header和content,content是由一个或多个backup sets构成的;
    • 镜像备份:一次备份操作创建一个相同的备份,最多三个镜像备份;
    • backup  family:多个备份设备和镜像备份构成backup family;
    • 一周一次完整备份,一天一次差异备份,一小时一次事务日志备份
    • 数据/日志的每次备份都使用一个单独的备份文件,数据备份的扩展名是 .bak,日志备份的扩展名是.trn

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

     

    还原 (Transact SQL) | Microsoft Docs   

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

    一,验证(Verifiy)备份文件

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

     

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

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

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

    SqlServer备份恢复生机,数据库还原。 

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

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

    --恢复数据库的一部分(部分还原)
    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 FILELISTONLY 
    FROM disk = 'D:TestDBBackupFolderSitedb_bak4.bak'
    --with file=1;
    

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

    完整备份

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

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

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

    --将特定页面还原到数据库(页面还原) 
    RESTORE DATABASE { database_name | @database_name_var }
    PAGE = 'file:page [ ,...n ]'
    [ , <file_or_filegroups> ] [ ,...n ]
    [ FROM <backup_device> [ ,...n ]SqlServer备份恢复生机,数据库还原。 ]
    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

    * *

    BACKUP DATABASE database_name 
    TO DISK  =  'physical_device_name'
    [ WITH { DIFFERENTIAL
    |  COPY_ONLY 
    | { COMPRESSION | NO_COMPRESSION } 
    | { NOINIT | INIT } 
    | { NOSKIP | SKIP } 
    | { NOFORMAT | FORMAT } 
    | 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 ]
    ]
    [;]

    差异备份

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

    优点

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

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

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

     

     

     

    1,完整备份和差异备份

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

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

    日志备份

     

     

     

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

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

     

    仅复制备份

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

     

     

    1. FullBackup1.bak
    2. DifferentialBackup2.bak
    3. LogBackup3.trn
    4. DifferentialBackup4.bak
    5. LogBackup5.trn
    6. 出现错误

    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.

     

     

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

    2,验证(Verify)备份文件

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

    恢复

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

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

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

     

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

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

    用于还原完整数据库备份的基本 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

     

     

     

     

     

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

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

     }

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

    将数据库还原到故障点

     

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

    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

    2,只复制(COPY_ONLY )备份

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

    <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

     

     

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

    The backup set on file 1 is valid.
    

    --备份设置选项 
    | 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 的恢复进程。

     

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

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

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

    3,压缩数据{ COMPRESSION | NO_COMPRESSION }

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

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

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

    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'
    

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

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

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

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

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

    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.

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

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

    二,还原数据文件

    <replication_WITH_option>::=
    | KEEP_REPLICATION

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

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

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

    <change_data_capture_WITH_option>::=
    | KEEP_CDC

    5,备份进度(stats)

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

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

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

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

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

    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.

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

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

    二,数据备份操作

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

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

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

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

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

    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,数据库差异备份,指定Differential选项**

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

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

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

    三,事务日志备份

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

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

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

    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. 

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

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

    1,尾日志备份

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

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

    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:

    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.

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

    2,日志截断

    4,重启选项(Restart)

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


    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. 

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

    四,事务日志备份

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

    1,正常情况下的事务日志备份

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

    backup log [TestSite]
    to disk = 'D:TestDBBackupFolderSitedb_bak3.trn'
    with
    compression,
    format,
    init,
    skip,
    stats=5
    

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

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

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

    backup log [TestSite]
    to disk = 'D:TestDBBackupFolderSitedb_bak4.trn'
    with
    compression,
    format,
    init,
    skip,
    stats=5,
    norecovery
    

    图片 2图片 3

     

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

    参考doc:

    View Code

    BACKUP (Transact-SQL).aspx)

    在还原事务日志时,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 包含的信息十分有限。

    图片 4

     

    参考文档:

    RESTORE (Transact-SQL).aspx)

    RESTORE VERIFYONLY (Transact-SQL).aspx)

    RESTORE FILELISTONLY (Transact-SQL).aspx)

    本文由新葡亰496net发布于网络数据库,转载请注明出处:SqlServer备份恢复生机,数据库还原

    关键词:

上一篇:新葡亰496netmysql常用言语,sql语法小结

下一篇:没有了