SQL Server的还原.docx

SQL Server的还原.docx

《SQL Server的还原.docx》由会员分享，可在线阅读，更多相关《SQL Server的还原.docx（11页珍藏版）》请在冰豆网上搜索。

SQLServer的还原

SQLServer的还原

1.差异备份的还原

不备份结尾日志的情况下还原数据

下面我们给出差异备份与还原的T-SQL方法，代码如下：

--创建备份设备

EXECsp_addumpdevice'disk','MyAdvWorks_1','d:

\backup\MyAdvWorks_1.bak';

EXECsp_addumpdevice'disk','MyAdvWorks_diff1','d:

\backup\MyAdvWorks_diff1.bak';

EXECsp_addumpdevice'disk','MyAdvWorks_log1','d:

\backup\MyAdvWorks_log1.bak';

EXECsp_addumpdevice'disk','MyAdvWorks_log2','d:

\backup\MyAdvWorks_log2.bak';

--数据库备份阶段

--创建完整备份

BACKUPDATABASEMyAdvWorks

TOMyAdvWorks_1

WITHINIT;

GO

--在完整备份之后创建差异备份

BACKUPDATABASEMyAdvWorks

TOMyAdvWorks_diff1

WITHDIFFERENTIAL;

GO

--数据库还原阶段

--完整还原

RESTOREDATABASEMyAdvWorks

FROMMyAdvWorks_1

WITHNORECOVERY;

GO

--在完整还原之后差异还原

RESTOREDATABASEMyAdvWorks

FROMMyAdvWorks_diff1

WITHRECOVERY;

GO

在执行上述完整还原的时候，SSMS报错，错误内容如下：

Msg3159,Level16,State1,Line2

Thetailofthelogforthedatabase"MyAdvWorks"hasnotbeenbackedup.UseBACKUPLOGWITHNORECOVERYtobackupthelogifitcontainsworkyoudonotwanttolose.UsetheWITHREPLACEorWITHSTOPATclauseoftheRESTOREstatementtojustoverwritethecontentsofthelog.

Msg3013,Level16,State1,Line2

RESTOREDATABASEisterminatingabnormally.

这是因为，对于使用完全恢复模式或大容量日志恢复模式的数据库，在大多数情况下，您必须在还原数据库前备份日志的结尾。

还原数据库而不首先备份日志的末尾将导致错误，除非RESTOREDATABASE语句包含WITHREPLACE或WITHSTOPAT子句，此子句必须指定数据备份的结束时间或在数据备份结束之后发生的事务。

有关结尾日志备份的详细信息，请参阅结尾日志备份（SQLServer）。

RESTORE的语法可以参考RESTORE（Transact-SQL）。

为了解决上述错误，我们将完整还原的TSQL语句修改如下：

--完整还原

RESTOREDATABASEMyAdvWorks

FROMMyAdvWorks_1

WITHREPLACE,NORECOVERY;

GO

REPLACE选项的影响

应尽可能避免使用REPLACE，而且在使用该选项之前必须仔细考虑。

还原一般会防止意外使用一个数据库覆盖另一个数据库。

如果RESTORE语句中指定的数据库已存在于当前服务器上，并且指定的数据库系列GUID与备份集中记录的数据库系列GUID不同，则不还原该数据库。

这是一项重要的安全保护措施。

使用REPLACE选项后，就会忽略还原时通常执行的几项重要安全检查。

忽略的检查如下：

还原时使用其他数据库的备份覆盖现有数据库。

使用REPLACE选项后，即使指定的数据库名称与备份集中记录的数据库名称不同，还原也允许您使用备份集中任何一个数据库覆盖现有数据库。

这会导致一个数据库意外覆盖另一个数据库。

在没有获取结尾日志备份并也没有使用STOPAT选项的情况下，使用完整恢复模式或大容量日志恢复模式对数据库进行还原。

使用REPLACE选项后，由于没有备份最近写入的日志，您会丢失提交的作业。

覆盖现有文件。

例如，可能会错误地覆盖错误类型的文件，如.xls文件或非联机状态的其他数据库正在使用的文件等。

如果覆盖现有文件，则即使所还原的数据库是完整的，也有可能丢失某些数据。

RECOVERY和NORECOVERY的比较

仔细观察上述完整还原和差异还原的TSQL语句，我们可以看到在完整还原阶段，使用了WITHNORECOVERY，而差异还原，则是使用了RECOVERY。

NORECOVERY指定不发生回滚。

从而使前滚按顺序在下一条语句中继续进行。

在这种情况下，还原顺序可还原其他备份，并执行前滚。

RECOVERY（默认值）表示，应在完成当前备份前滚之后执行回滚。

恢复数据库要求要还原的整个数据集（“前滚集”）必须与数据库一致。

如果前滚集尚未前滚到与数据库保持一致的地步，并且指定了RECOVERY，则数据库引擎将发出错误。

因此RESTORE...WITHRECOVERY状态就表明此时数据库已经处于一致性状态了。

一般都是数据库还原的最后一步才会使用RESTORE...WITHRECOVERY，其他时候都是使用RESTORE...WITHNORECOVERY。

前滚与回滚的区别

前滚和回滚是SQLServer中的两个事务操作。

前滚用于恢复之前的操作。

　　比如：

执行一个删除一条记录的命令，刚刚把该操作的日志写完（即在日志中已经commit），并准备执行删除操作时，但是此时停电了，因此这条记录的删除工作没有完成。

在下次启动SQLServer时，数据库会读取日志最近的一个检查点（checkpoint），然后重做所有已经commit的事务，这就包括之前删除记录的操作。

回滚用于撤销之前的操作。

　　比如：

我要执行一个银行转账的事务，从账户A中转出1000元到账户B中，在我执行完A-1000的时候，数据库down掉了，此时整个事务还没有执行完，因此事uncommitted的。

所以在下次启动SQLServer的时候，数据库会读取日志最近的一个检查点（checkpoint），回滚所有那些uncommitted的事务。

比如在这里会撤销A-1000的操作，也就是会往A账户中加回去1000元。

2.事务日志的还原

在数据库中，使用最频繁的应该是事务日志的备份，那么下面就讲讲事务日志的备份与还原。

TSQL代码如下：

复制代码

--开始：

实验2：

事务日志备份与---------------------------------------------

--创建备份设备

EXECsp_addumpdevice'disk','MyAdvWorks_1','d:

\backup\MyAdvWorks_1.bak';

EXECsp_addumpdevice'disk','MyAdvWorks_diff1','d:

\backup\MyAdvWorks_diff1.bak';

EXECsp_addumpdevice'disk','MyAdvWorks_log1','d:

\backup\MyAdvWorks_log1.bak';

EXECsp_addumpdevice'disk','MyAdvWorks_log2','d:

\backup\MyAdvWorks_log2.bak';

--数据库备份阶段

--创建完整备份

BACKUPDATABASEMyAdvWorks

TOMyAdvWorks_1

WITHINIT;

GO

--在完整备份之后创建差异备份

BACKUPDATABASEMyAdvWorks

TOMyAdvWorks_diff1

WITHDIFFERENTIAL;

GO

--事务日志备份

BACKUPLOGMyAdvWorks

TOMyAdvWorks_log1;

GO

--数据库还原阶段----------

--结尾日志备份，结尾日志备份在还原阶段，在restore之前先进行结尾日志备份

usemaster

BACKUPLOGMyAdvWorks

TOMyAdvWorks_log2

WITHINIT,

NORECOVERY;

GO

--完整还原

RESTOREDATABASEMyAdvWorks

FROMMyAdvWorks_1

WITHNORECOVERY;

GO

--差异还原

RESTOREDATABASEMyAdvWorks

FROMMyAdvWorks_diff1

WITHNORECOVERY;

GO

--事务日志还原

RESTORELOGMyAdvWorks

FROMMyAdvWorks_log1

WITHNORECOVERY;

GO

--结尾日志还原

RESTORELOGMyAdvWorks

FROMMyAdvWorks_log2

WITHRECOVERY;

GO

--结束：

实验2---------------------------------------------

复制代码

3.备份到一个逻辑设备中

观察上面的备份我们可以发现，我们创建了四个逻辑备份设备，分别用户存储完整备份，差异备份，事务日志备份和结尾日志备份。

然后恢复的时候也是指定到这四个不同的备份设备，但是当我们只创建一个备份设备，并且备份到同样一个备份设备的时候。

备份工作可以完成，还原工作就不能完成了。

比如如何区分差异还原和完全还原，如何区分事务日志还原和结尾日志还原。

因此无法通过TSQL进行精确还原。

但是使用SSMS的话，我们可以在一个逻辑设备，并且进行精确还原。

解决问题（PS:

2012-7-9）

今天在使用restorestopat的时候看到了restore中有file关键字，并且在备份的时候在message中看到了file1等字段，所以我就想这个file是不是就是备份集呢？

后来验证是的，因此也就找到了使用一个逻辑设备进行备份还原的方法。

代码示例如下

--开始：

实验2：

事务日志备份与---------------------------------------------

--创建备份设备

EXECsp_addumpdevice'disk','MyAdvWorks_1','d:

\backup\MyAdvWorks_1.bak';

--数据库备份阶段

--创建完整备份

BACKUPDATABASEMyAdvWorks

TOMyAdvWorks_1

WITHINIT;

GO

/*

Processed280pagesfordatabase'MyAdvWorks',file'MyAdvWorks'onfile1.

Processed2pagesfordatabase'MyAdvWorks',file'MyAdvWorks_log'onfile1.

BACKUPDATABASEsuccessfullyprocessed282pagesin0.257seconds（8.568MB/sec）.

*/

--在完整备份之后创建差异备份

BACKUPDATABASEMyAdvWorks

TOMyAdvWorks_1

WITHDIFFERENTIAL;

GO

/*

Processed40pagesfordatabase'MyAdvWorks',file'MyAdvWorks'onfile2.

Processed1pagesfordatabase'MyAdvWorks',file'MyAdvWorks_log'onfile2.

BACKUPDATABASEWITHDIFFERENTIALsuccessfullyprocessed41pagesin0.108seconds（2.965MB/sec）.

*/

--事务日志备份

BACKUPLOGMyAdvWorks

TOMyAdvWorks_1;

GO

/*

Processed4pagesfordatabase'MyAdvWorks',file'MyAdvWorks_log'onfile3.

BACKUPLOGsuccessfullyprocessed4pagesin0.078seconds（0.338MB/sec）.

*/

--数据库还原阶段----------

--结尾日志备份，结尾日志备份在还原阶段，在restore之前先进行结尾日志备份

BACKUPLOGMyAdvWorks

TOMyAdvWorks_1

WITHNORECOVERY;

GO

/*

Processed1pagesfordatabase'MyAdvWorks',file'MyAdvWorks_log'onfile4.

BACKUPLOGsuccessfullyprocessed1pagesin0.059seconds（0.041MB/sec）.

*/

--完整还原

RESTOREDATABASEMyAdvWorks

FROMMyAdvWorks_1

WITHFILE=1,

NORECOVERY;

GO

/*

Processed280pagesfordatabase'MyAdvWorks',file'MyAdvWorks'onfile1.

Processed2pagesfordatabase'MyAdvWorks',file'MyAdvWorks_log'onfile1.

RESTOREDATABASEsuccessfullyprocessed282pagesin0.363seconds（6.066MB/sec）.

*/

--差异还原

RESTOREDATABASEMyAdvWorks

FROMMyAdvWorks_1

WITHFILE=2,

NORECOVERY;

GO

/*

Processed40pagesfordatabase'MyAdvWorks',file'MyAdvWorks'onfile2.

Processed1pagesfordatabase'MyAdvWorks',file'MyAdvWorks_log'onfile2.

RESTOREDATABASEsuccessfullyprocessed41pagesin0.129seconds（2.483MB/sec）.

*/

--事务日志还原

RESTORELOGMyAdvWorks

FROMMyAdvWorks_1

WITHFILE=3,

NORECOVERY;

GO

/*

Processed0pagesfordatabase'MyAdvWorks',file'MyAdvWorks'onfile3.

Processed4pagesfordatabase'MyAdvWorks',file'MyAdvWorks_log'onfile3.

RESTORELOGsuccessfullyprocessed4pagesin0.044seconds（0.599MB/sec）.

*/

--结尾日志还原

RESTORELOGMyAdvWorks

FROMMyAdvWorks_1

WITHFILE=4,

RECOVERY;

GO

/*

Processed0pagesfordatabase'MyAdvWorks',file'MyAdvWorks'onfile4.

Processed1pagesfordatabase'MyAdvWorks',file'MyAdvWorks_log'onfile4.

RESTORELOGsuccessfullyprocessed1pagesin0.019seconds（0.128MB/sec）.

*/

--结束：

实验2---------------------------------------------

在备份的时候，message中显示文件被备份到了哪一个file中，所以在restore的时候，指定file序号就可以执行还原了。