014_完整恢复模式

014_完整恢复模式

 

 

 

 

 

 

--【完整恢复模型】***************************************************************************

 

 

--将恢复模式切换到完整恢复模式

ALTER DATABASE test SET RECOVERY FULL

 

 

--创建备份设备*************************************************************

 

--创建完整备份设备

EXEC SP_addumpdevice @devtype=N'disk',@logicalname=N'test_device_full',@physicalname=N'E:\sql_2008\test_backup01\test_device_full.bak'

--创建差异备份设备

EXEC SP_addumpdevice @devtype=N'disk',@logicalname=N'test_device_diff01',@physicalname=N'E:\sql_2008\test_backup01\test_device_diff_01.bak'

EXEC SP_addumpdevice @devtype=N'disk',@logicalname=N'test_device_diff02',@physicalname=N'E:\sql_2008\test_backup01\test_device_diff_02.bak'

--创建日志备份设备

EXEC SP_addumpdevice @devtype=N'disk',@logicalname=N'test_device_log01',@physicalname=N'E:\sql_2008\test_backup01\test_device_log_01.bak'

EXEC SP_addumpdevice @devtype=N'disk',@logicalname=N'test_device_log02',@physicalname=N'E:\sql_2008\test_backup01\test_device_log_02.bak'

EXEC SP_addumpdevice @devtype=N'disk',@logicalname=N'test_device_log03',@physicalname=N'E:\sql_2008\test_backup01\test_device_log_03.bak'

EXEC SP_addumpdevice @devtype=N'disk',@logicalname=N'test_device_endlog',@physicalname=N'E:\sql_2008\test_backup01\test_device_endlog.bak'

 

--移除备份设备

--EXEC SP_DROPDEVICE test_device_ENDLOG

 

--备份步骤

 

--完整备份整个库【完整】

backup database test to test_device_full with init

 

 

--修改数据库【1】

CREATE TABLE test_full_01(id int)

--日志备份【【日志】】

backup log test to test_device_log01 with init

--修改数据库【2】

CREATE TABLE test_full_02(id int)

--日志备份【【日志】】

backup log test to test_device_log01

 

 

--修改数据库【3】

CREATE TABLE test_full_03(id int)

--差异备份

backup database test to test_device_diff01 with differential

 

 

--修改数据库【4】

CREATE TABLE test_full_04(id int)

--日志备份【【日志】】

backup LOG test to test_device_log02 with init

--修改数据库【5】 事物标志：test_tran_full01

BEGIN TRANSACTION test_tran_full01 WITH MARK 'not the mark name, just a description' 

CREATE TABLE test_full_05(id int)

commit

--日志备份【【日志】】

backup log test to test_device_log02

 

 

--修改数据库【6】

CREATE TABLE test_full_06(id int)

--差异备份

backup database test to test_device_diff02 with differential

 

 

--修改数据库【7】

CREATE TABLE test_full_07(id int)

--日志备份【【日志】】

backup log test to test_device_log03 with init

--修改数据库【8】 事物标志:test_tran_full02

BEGIN TRANSACTION test_tran_full02 WITH MARK 'not the mark name, just a description' 

CREATE TABLE test_full_08(id int)

commit

--日志备份【【日志】】

backup log test to test_device_log03

 

 

--修改数据库【9】  此时出现错误

CREATE TABLE test_full_09(id int)

 

 

 

--恢复步骤***************************************************************************

 

--切换到master数据库

USE master

 

--尾部日志备份

backup LOG test to test_device_endlog WITH no_truncate,FORMAT

--当尾部日志损坏时

         --BACKUP LOG test TO test_backup_endlog WITH NO_TRUNCATE

 

--恢复到初始库

restore DATABASE test from test_device_full WITH norecovery

--中间忽略了两个日志备份的恢复，直接做差异备份的恢复

RESTORE DATABASE test FROM test_device_diff01 WITH norecovery

--中间忽略了两个日志备份的恢复，直接做差异备份的恢复

RESTORE DATABASE test FROM test_device_diff02 WITH norecovery

--做日志备份的恢复

RESTORE log test FROM test_device_log03 WITH FILE=1,NORECOVERY

--做日志备份的恢复（事物标记还原）

 

RESTORE log test FROM test_device_log03 WITH FILE=2,NORECOVERY,STOPATMARK =N'test_tran_full02';

/*

--做日志备份的恢复（时间点还原）

         --RESTORE log test FROM test_device_log03 WITH FILE=2, STOPAT = '5/11/2014 10:12:44 PM';

--做日志备份的恢复（lsn还原）

         --RESTORE log test FROM test_device_log03 WITH FILE=2,NORECOVERY,STOPATMARK = 'lsn:15000000040000037';

*/

 

 

--倒数第二条恢复的为日志，则用此语句结束

RESTORE log test WITH recovery

--倒数第二条恢复的为数据，则用此语句结束

         --RESTORE DATABASE test WITH recovery

 

 

 

 

 

--【with 选项说明】***************************************************************************

/*

with format/noformat

         指定是否应该在用于此备份操作的卷上写入介质标头，以覆盖任何现有的介质标头和备份集。默认为保留现的有介质标头和备份集(noformat)

 

with init/noinit

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

with differential

         只能与 BACKUP DATABASE 一起使用，指定数据库备份或文件备份应该只包含上次完整备份后更改的数据库或文件部分。

        

with norecovery

         备份日志的尾部并使数据库处于 RESTORING 状态。当将故障转移到辅助数据库或在执行 RESTORE 操作前保存日志尾部时，NORECOVERY 很有用。

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

 

with no_truncate

         指定不截断日志，并使数据库引擎尝试执行备份，而不考虑数据库的状态。

 

with skip/noskip

         控制备份操作是否在覆盖介质中的备份集之前检查它们的过期日期和时间。默认为覆盖介质上的所有备份集之前先检查它们的过期日期(noskip)。

 

with copy_only

         与 BACKUP DATABASE 一起使用时，COPY_ONLY 选项创建的完整备份不能用作差异基准。

         与 BACKUP LOG 一起使用时，COPY_ONLY 选项将创建“仅复制日志备份”，该备份不会截断事务日志。

        

with compression

         指定对于此备份是否执行备份压缩。SQL Server 2008 Enterprise 中引入了备份压缩。

*/

 

 

 

 

 

--1.查看错误日志

/*

查看windows的系统日志记录

         win+r\eventvwr\系统

查看sqlserver错误日志（总共有7个错误日志文件，0表示当前文件，6表示最早的一个文件）

         exec xp_readerrorlog 1

*/

--2.仅当第一次备份数据库时，或者由简单恢复模式切换到完整恢复模式或大容量日志恢复模式时，会产生一个新的‘日志链’.

--3.除非在创建完整数据库备份时选择覆盖现有备份集，否则现有的日志链将保持不变。

--4.结尾日志备份

/*

         ①、如果数据库处于联机状态，每当您准备对数据库执行的下一个操作为还原操作时，请在开始还原顺序之前使用 WITH NORECOVERY 备份日志尾部：

         ②、如果数据库处于脱机状态并且无法启动。尝试执行结尾日志备份。由于此时不会发生任何事务，所以 WITH NORECOVERY 是可选的。如果数据库受损，请使用 WITH CONTINUE_AFTER_ERROR 或 WITH NO_TRUNCATE。

*/

--5.FILESTREAM数据：每天创建的大部分数据（如文本文档、图像和视频）均为非结构化的数据。这类非结构化的数据通常存储在数据库之外，以便与数据库中的结构化数据分隔开来。

--6.恢复到特定时间点

         --如果部分还原顺序不包括任何 FILESTREAM 文件组，则不支持时点还原。可以强制继续执行还原顺序。但在 RESTORE 语句中省略的 FILESTREAM 文件组将永远无法还原。若要强制执行时点还原，请指定 CONTINUE_AFTER_ERROR 选项以及 STOPAT、STOPATMARK 或 STOPBEFOREMARK 选项，还必须在随后的 RESTORE LOG 语句中指定后面的三个选项。如果指定 CONTINUE_AFTER_ERROR，则部分还原顺序将成功，但 FILESTREAM 文件组将不可恢复。

--7.恢复到日志序列号 (LSN)

--标识可用于所有事务日志备份的最新标记事务。此信息存储在每个服务器的 msdb 数据库中的 logmarkhistory 表中。

select * from msdb..logmarkhistory

--8.在事务日志中插入命名标记的 Transact-SQL 语法,可选的 description 是标记的文本说明而不是标记名,语句中创建的事务和标记的名称均为 Tx1.

--9.删除现有数据库，从备份中重建数据库

restore database d5 from bak6 with replace  

--10.备份文件后缀名

/*

数据备份文件  .bak

差异备份文件  .bak

日志文件      .dif

文件组/夹文件 .bck

*/

--11.恢复数据库报错时，可离线后再上线数据库即可