sqlserver数据库备份与还原_sqlserver norecovery

一丶数据库备份

BACKUP DATABASE {database_name | @database_name_var }  --数据库名

TO < backup_device> [ ,...n ]  --备份到磁盘地址，bak格式是备份文件格式

//各种属性

 

[ WITH

    [BLOCKSIZE = { blocksize | @blocksize_variable } ]

    [ [ ,] DESCRIPTION = { 'text' | @text_variable } ]

    [ [ ,] DIFFERENTIAL ]

    [ [ ,] EXPIREDATE = { date | @date_var }

       | RETAINDAYS = { days | @days_var } ]

    [ [ ,] PASSWORD = { password | @password_variable } ] --为备份设置密码（字符型）

    [ [ ,] FORMAT | NOFORMAT ]

    [ [ ,] { INIT | NOINIT } ]

    [ [ ,] NAME = { backup_set_name | @backup_set_name_var } ]

    [ [ ,] { NOSKIP | SKIP } ]

    [ [ ,] { NOREWIND | REWIND } ]

    [ [ ,] { NOUNLOAD | UNLOAD } ]

    [ [ ,] RESTART ]

    [ [ ,] STATS [ = percentage ] ] 

]

 

 

例：

backup database[TestSite]

to disk ='D:\TestDBBackupFolder\Sitedb_bak1.bak' --specify new backup file

with

compression,

format,

init,

skip,

stats=5

 

backup database test

from disk = 'c:\test_stripping.bak',  

 disk = 'd:\test_stripping.bak',  

  disk = 'e:\test_stripping.bak'      

with replace                        --替换原来的数据库

 

 

属性详解

完整备份当然就是将数据库的数据全部备份

而差异备份指得是将上一次备份后到现在对数据的修改进行备份.

因此差异备份不能单独使用,只能先还原上一次的完整备份后,才能还原差异备份,

 

DIFFERENTIAL 

如果将 DIFFERENTIAL 和 COPY_ONLY 一起使用，则忽略 COPY_ONLY 并创建差异备份。

 

 

DESCRIPTION ={ 'text' | @text_variable }*
指定说明备份集的自由格式文本。 该字符串最长可达 255 个字符。就是个备注

 

RESTORE HEADERONLY  

   FROMDISK='D:\backup\Sitedb_bak1.bak' 

   WITHNOUNLOAD 

GO 

这个语句可以查询信息。

 

COPY_ONLY 适用范围：SQL Server 和 SQL 数据库托管实例。指定备份为“仅复制备份”，该备份不影响正常的备份顺序。 仅复制备份是独立于定期计划的常规备份而创建的。 仅复制备份不会影响数据库的总体备份和还原过程。

应在出于特殊目的而进行备份的情况下使用仅复制备份，例如在进行联机文件还原前备份日志。 通常，仅复制日志备份仅使用一次即被删除。

·        与 BACKUP DATABASE 一起使用时，COPY_ONLY 选项创建的完整备份不能用作差异基准。 差异位图不会被更新，因此差异备份的表现就像仅复制备份不存在一样。 后续差异备份将最新的常规完整备份用作它们的基准。

·        与 BACKUP LOG 一起使用时，COPY_ONLY 选项将创建“仅复制日志备份”，该备份不会截断事务日志。 仅复制日志备份对日志链没有任何影响，因此其他日志备份的表现就像仅复制备份不存在一样。

 

 

{ COMPRESSION| NO_COMPRESSION }
仅适用于 SQL Server 2008 Enterprise 和更高版本；指定是否对此备份执行备份压缩，覆盖服务器级默认设置。安装时，默认行为是不进行备份压缩。

 

NAME = { backup_set_name | @backup_set_var }
指定备份集的名称。 名称最长可达 128 个字符。 如果未指定 NAME，它将为空。

 

RESTORE HEADERONLY  

   FROMDISK='D:\backup\Sitedb_bak1.bak' 

   WITHNOUNLOAD 

GO 

可查出。

 

{ EXPIREDATE ='date' | RETAINDAYS = 天数 }
指定允许覆盖该备份的备份集的日期。 如果同时使用这两个选项，RETAINDAYS 的优先级别将高于 EXPIREDATE。

在 BACKUP 语句中，指定EXPIREDATE 或 RETAINDAYS 选项以便确定 SQL Server 数据库引擎 何时可以覆盖备份。 如果这两个选项均未指定，则过期日期由 介质保持期 服务器配置设置确定。 下面的示例使用 EXPIREDATE 选项指定过期日期为 2015 年 6 月 30 日 (6/30/2015)。

 

如果这两个选项均未指定，则过期日期由 mediaretention 配置设置确定。

这些选项仅仅阻止 SQL Server 覆盖文件。

EXPIREDATE = { 'date' | @date_var } 指定备份何时到期并可覆盖。如果作为变量 (@* date_var) 提供，则该日期必须采用已配置系统日期/时间的格式，并指定为下列类型之一：

·        字符串常量 (@date_var = date)

·        字符串数据类型（ntext 或 text 数据类型除外）的变量

·        smalldatetime

·        datetime 变量

例如：

·        'Dec 31, 2020 11:59 PM'

·        '1/1/2021'

可用上述查询语句查询，日期必须与系统日期格式保持一致。

以上信息皆可在下列语句中体现

RESTORE HEADERONLY  

   FROMDISK='D:\backup\Sitedb_bak1.bak' 

   WITHNOUNLOAD 

GO

媒体集选项

这些选项作为一个整体对介质集进行操作。

{ NOINIT | INIT }
控制备份操作是追加到还是覆盖备份介质中的现有备份集。 默认为追加到介质中最新的备份集 (NOINIT)。

NOINIT
表示备份集将追加到指定的介质集上，以保留现有的备份集。 如果为介质集定义了介质密码，则必须提供密码。 NOINIT是默认设置。

NOFORMAT
指定备份操作在用于此备份操作的介质卷上保留现的有介质标头和备份集。 这是默认行为。

{ NOSKIP | SKIP }
控制备份操作是否在覆盖介质中的备份集之前检查它们的过期日期和时间。

FORMAT
指定创建新的介质集。 FORMAT 将使备份操作在用于备份操作的所有介质卷上写入新的介质标头。卷的现有内容将变为无效，因为覆盖了任何现有的介质标头和备份集。

重要

请谨慎使用 FORMAT。 格式化介质集的任何一个卷都将使整个介质集不可用。 例如，如果初始化现有条带介质集中的单个磁带，则整个介质集都将变得不可用。

指定 FORMAT 即表示 SKIP；SKIP 无需显式声明。

若要忽略过期日期，请使用 SKIP 选项。

数据传输选项

BUFFERCOUNT = { buffercount | @buffercount_variable }
指定用于备份操作的 I/O 缓冲区总数。 可以指定任何正整数；但是，较大的缓冲区数可能导致由于 Sqlservr.exe 进程中的虚拟地址空间不足而发生“内存不足”错误。

缓冲区使用的总计空间由以下内容确定：buffercount/maxtransfersize。

MAXTRANSFERSIZE = { maxtransfersize | @* maxtransfersize_variable* } 指定要在 SQLServer 和备份介质之间使用的最大传输单元（字节）。 可能的值是 65536 字节 (64 KB) 的倍数，最多可到 4194304 字节 (4 MB)。

影响备份速度。

错误管理选项

使用这些选项可以确定是否为备份操作启用了备份校验和，以及备份操作是否在遇到错误时停止。

{ NO_CHECKSUM | CHECKSUM }
控制是否启用备份校验和。

使用这些选项可以确定是否为备份操作启用了备份校验和，以及备份操作是否将在遇到错误时停止。

启用的话 hasBackupChecksums 变为1

NO_CHECKSUM
显式禁用备份校验和的生成（以及页校验和的验证）。 这是默认行为。

CHECKSUM
如果此选项已启用并且可用，则指定备份操作将验证每页的校验和及页残缺，并生成整个备份的校验和。

使用备份校验和可能会影响工作负荷以及备份吞吐量。

{ STOP_ON_ERROR |CONTINUE_AFTER_ERROR }
控制备份操作在遇到页校验和错误后是停止还是继续。

STOP_ON_ERROR
如果未验证页校验和，则指示 BACKUP 失败。 这是默认行为。

CONTINUE_AFTER_ERROR
指示 BACKUP 继续执行，不管是否遇到无效校验和或页撕裂之类的错误。

数据库损坏时，如果无法使用 NO_TRUNCATE 选项备份日志尾部，则可以通过指定CONTINUE_AFTER_ERROR 而不是 NO_TRUNCATE 尝试执行尾日志备份。

监视选项

STATS [ = percentage ]
每当另一个百分比完成时显示一条消息，并用于测量进度。 如果省略百分比，则 SQL Server 在每完成 10% 就显示一条消息。

STATS 选项报告截止报告下一个间隔的阈值时的完成百分比。 这是指定百分比的近似值；例如，当 STATS=10 时，如果完成进度为 40%，则该选项可能显示 43%。 对于较大的备份集，这不是问题，因为完成百分比在已完成的 I/O 调用之间变化非常缓慢。

 

日志备份
适用范围：SQL Server
这些选项仅与 BACKUP LOG 一起使用。

日志是数据库的完整操作记录

查看日志：执行语句 exec xp_readerrorlog

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

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

 

SQL Server 2005及以上版本也可以使用下面这条语句来查看:
SELECT name, recovery_model_desc FROMmaster.sys.databases ORDER BY name

如果想改变数据库的恢复模型，可以使用下面SQL语句:

简单恢复模型:ALTER DATABASEAdventureWorks SET RECOVERY SIMPLE
完整恢复模型:ALTER DATABASE AdventureWorksSET RECOVERY FULL
批量日志恢复模型:ALTER DATABASE AdventureWorks SETRECOVERY BULK_LOGGED

 

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

 

{ NORECOVERY |STANDBY = undo_file_name }
NORECOVERY 适用范围：SQL Server
备份日志的尾部并使数据库处于 RESTORING 状态。 当将故障转移到辅助数据库或在执行 RESTORE 操作前保存日志尾部时，NORECOVERY 很有用。

若要执行最大程度的日志备份（跳过日志截断）并自动将数据库置于 RESTORING 状态，请同时使用 NO_TRUNCATE 和 NORECOVERY 选项。

STANDBY =standby_file_name
适用范围：SQL Server*******
备份日志的尾部并使数据库处于只读和 STANDBY 状态。 将 STANDBY 子句写入备用数据（执行回滚，但需带进一步还原选项）。 使用 STANDBY 选项等同于 BACKUP LOG WITHNORECOVERY 后跟 RESTORE WITHSTANDBY。

使用备用模式需要一个备用文件，该文件由 standby_file_name指定，其位置存储于数据库的日志中。 如果指定的文件已经存在，则 数据库引擎会覆盖该文件；如果指定的文件不存在，则 数据库引擎将创建它。 备用文件将成为数据库的一部分。

该文件将保存对回滚所做的更改，如果要在以后应用 RESTORE LOG 操作，则必须反转这些更改。 必须有足够的磁盘空间供备用文件增长，以使备用文件能够包含数据库中由回滚的未提交事务修改的所有不重复的页。

NO_TRUNCATE
适用范围：SQL Server
指定不截断日志，并使 数据库引擎 尝试执行备份，而不考虑数据库的状态。 因此，使用 NO_TRUNCATE 执行的备份可能具有不完整的元数据。 该选项允许在数据库损坏时备份日志。

BACKUP LOG 的 NO_TRUNCATE 选项相当于同时指定 COPY_ONLY 和 CONTINUE_AFTER_ERROR。

如果不使用 NO_TRUNCATE 选项，则数据库必须处于 ONLINE 状态。 如果数据库处于 SUSPENDED 状态，则可以通过指定 NO_TRUNCATE 来创建备份。 但是，如果数据库处于 OFFLINE 或 EMERGENCY 状态，即便使用了 NO_TRUNCATE，也不允许执行 BACKUP。

二丶数据还原

SQL Server 2017 无法还原使用 SQLServer 的早期版本创建的 master、model 和 msdb 备份。

注意： SQLServer 备份不会还原到比创建了备份的版本还早的 SQLServer 版本。

SQL Server 的每个版本使用的默认路径与早期版本不同。 因此，若要还原在早期版本备份的默认位置创建的数据库，必须使用 MOVE 选项。

--replace 覆盖原有数据库 
--recovery 还原数据库后，数据库处于正常状态
--norecovery 还原数据库后，数据库处于非正常状态，等待下一步还原

 

--To Restore an Entire Database from a Full databasebackup (a Complete Restore): 

RESTORE DATABASE { database_name | @database_name_var}    

 [ FROM <backup_device>[ ,...n ] ]  --物理地址

 [ WITH  

   { 

    [ RECOVERY |NORECOVERY | STANDBY =  

       {standby_file_name | @standby_file_name_var }  

       ] 

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

 ] 

[;] 

 

WITH 选项

[ RECOVERY | NORECOVERY | STANDBY ]
支持的语句：RESTORE

RECOVERY
指示还原操作回滚任何未提交的事务。 在恢复进程后即可随时使用数据库。 如果既没有指定 NORECOVERY 和 RECOVERY，也没有指定 STANDBY，则默认为 RECOVERY。

如果安排了后续 RESTORE 操作（RESTORE LOG 或从差异数据库备份 RESTORE DATABASE），则应改为指定 NORECOVERY 或 STANDBY。

从 SQL Server 早期版本中还原备份集时，可能要求将数据库升级。 如果指定了 WITH RECOVERY，升级将自动进行。

如果省略 FROM 子句，则必须在 WITH 子句中指定 NORECOVERY、RECOVERY 或 STANDBY。

NORECOVERY
指示还原操作不回滚任何未提交的事务。 如果稍后必须应用另一个事务日志，则应指定NORECOVERY 或 STANDBY 选项。 如果既没有指定 NORECOVERY 和 RECOVERY，也没有指定 STANDBY，则默认为 RECOVERY。 使用 NORECOVERY 选项执行脱机还原操作时，数据库将无法使用。

还原数据库备份和一个或多个事务日志时，或者需要多个 RESTORE 语句（例如还原一个完整数据库备份并随后还原一个差异数据库备份）时，RESTORE 需要对所有语句使用 WITH NORECOVERY 选项，但最后的 RESTORE 语句除外。 最佳方法是按多步骤还原顺序对所有语句都使用 WITHNORECOVERY，直到达到所需的恢复点为止，然后仅使用单独的 RESTORE WITH RECOVERY 语句执行恢复。

REPLACE
支持的语句：RESTORE

指定即使存在另一个具有相同名称的数据库， SQL Server 也应该创建指定的数据库及其相关文件。在这种情况下将删除现有的数据库。 如果不指定 REPLACE 选项，则会执行安全检查。 这样可以防止意外覆盖其他数据库。 安全检查可确保在以下条件同时存在的情况下，RESTORE DATABASE 语句不会将数据库还原到当前服务器：

·        在 RESTORE 语句中命名的数据库已存在于当前服务器中，并且

·        该数据库名称与备份集中记录的数据库名称不同。

若无法验证现有文件是否属于正在还原的数据库，则 REPLACE 也允许 RESTORE 覆盖该文件。RESTORE 通常拒绝覆盖已存在的文件。 WITH REPLACE也可以同样的方式用于 RESTORE LOG 选项。

REPLACE 还会覆盖在恢复数据库之前备份尾日志的要求。

 

RESTART
支持的语句：RESTORE

指定 SQL Server 应重新启动被中断的还原操作。 RESTART 从中断点重新启动还原操作。

restore log test  

from disk = 'c:\test_log_2.trn'  

with recovery  --完成还原，使数据库处于online状态

 

--2.2当需要把当前的备份在其他服务器上还原时可以指定新的物理路径      

restore database test_other  --新的数据库名称  

from disk = 'c:\test_1.bak'  

with file = 2,  

move 'test'     to 'c:\test_other.mdf',    --数据的逻辑名称->新的物理路径  

 move 'test_log' to 'c:\test_other_log.log' --日志的逻辑名称->新的物理路径  

 

--4.7还原完全备份 

restore database test  

from disk = 'c:\test_full_1.bak'  

with replace,            --由于原来的数据库还存在,所以必须替换  

       norecovery          --必须指定此选项，让数据库处于restoring，这样才能继续还原

 

一直处于状态用下列语句恢复

Restorelog 你的库名 with recovery