在将论坛数据库备份之后,2. 备份SQL语句的使用

发布时间:2020-05-02  栏目:数据  评论:0 Comments

SQL Server数据库备份与还原处理

在今天的文章里我想谈下每个DBA应该知道的一个重要话题:在SQL
Server里如何进行页级别还原操作。假设在SQL
Server里你有一个损坏的页,你要从最近的数据库备份只还原有问题的页,而不是还原整个数据库。

数据库恢复是和数据库备份相对应的操作,它是将数据库备份重新加载到系统中的过程。数据库恢复可以创建备份完成时数据库中存在的相关文件,但是备份以后的所有数据库修改都将丢失。

辛苦忙碌了一个星期终于安装配置好了TFS服务器,给每个团队成员分配了账户和邮箱。不过,老机器中的部分数据需要备份到新机器中,其中在移植一个使用DVBBS架设的论坛的时候,出了点问题,记录如下,以备查找,也希望能帮助到其他人。

转贴路径:

我们来破坏一个页

第一步我想向你展示下如何建立表(或索引)里有个特定页损坏的情景,这里我们会进行一些魔术,因为开箱即用(out-of-box)的SQL
Server本身不会引入任何损坏的页(如果有的话,恭喜你找到了一个BUG)。我们从创建一个新的数据库,往新建的表插入一些记录开始。

 1 USE master 2 GO 3  4 CREATE DATABASE PageLevelRestores 5 GO 6  7 USE PageLevelRestores 8 GO 9 10 -- Create a table where every record fits onto 1 page of 8kb11 CREATE TABLE Test12 (13   Filler CHAR(8000)14 )15 GO16 17 -- Insert 4 records18 INSERT INTO Test VALUES (REPLICATE('A', 8000))19 INSERT INTO Test VALUES (REPLICATE('B', 8000))20 INSERT INTO Test VALUES (REPLICATE('C', 8000))21 INSERT INTO Test VALUES (REPLICATE('D', 8000))22 GO23 24 -- Retrieve the selected records25 SELECT * FROM Test26 GO

下一步我进行完整数据库备份。这就是说这个备份包含了属于Test表的所有页。这非常重要,因为接下来我们会破坏这个表的一个特定页。为了找出属于Test表的页,我用DBCC
IND
命令来返回所有属于这个表的页。

1 -- Perform a full database backup2 BACKUP DATABASE PageLevelRestores TO DISK = N'C:\Backups\PageLevelRestores.bak'3 GO4 5 -- Retrieve the first data page for the specified table (columns PageFID and PagePID)6 DBCC IND(PageLevelRestores, Test, -1)7 GO

图片 1

图片 2

为了破坏一个特定的页,我使用未公开的DBCC WRITEPAGE命令。是的,在SQL
Server里有个可用的DBCC WRITEPAGE命令,但请不要告诉任何人……

 1 ALTER DATABASE PageLevelRestores SET SINGLE_USER WITH ROLLBACK IMMEDIATE 2 GO 3  4 -- Let's corrupt page 90... 5 DBCC WRITEPAGE(PageLevelRestores, 1, 90, 0, 1, 0x41, 1) 6 DBCC WRITEPAGE(PageLevelRestores, 1, 90, 1, 1, 0x41, 1) 7 DBCC WRITEPAGE(PageLevelRestores, 1, 90, 2, 1, 0x41, 1) 8 GO 9 10 ALTER DATABASE PageLevelRestores SET MULTI_USER11 GO

为了使用DBCC
WRITEPAGE
,问题数据库必须设置如代码所示的单用户模式(Single-User
mode
)。这里我模拟了有个存储错误,写了一些垃圾到存储的页里(是的,这个在你工作中也会碰到!)。现在当你从表再次读取数据库,SQL
Server会返回你一个824 I/O错误,因为对损坏页的校验失败了。

1 -- Retrieve the selected records2 SELECT * FROM Test3 GO

图片 3

一旦SQL
Server在I/O访问期间检测到一个损坏的页,在msdb.dbo.suspect_pages里,损坏的页也会被记录,如下图所示。

1 SELECT * FROM msdb.dbo.suspect_pages

图片 4

对msdb里对特定表进行监控是个很好的想法,可以得到你的数据库里是否有损坏的页。现在我们让事情变得更糟糕,下列代码会往表里插入另外一条记录。

1 -- Now we have additional transaction that we don't want to loose...2 INSERT INTO Test VALUES (REPLICATE('E', 8000))3 GO

   SQL
Server进行数据库恢复时,系统将自动进行安全性检查,以防止误操作而使用了不完整的信息或其他的数据备份覆盖现有的数据库。当出现以下几种情况时,系统将不能恢复数据库。

由于论坛已经运行了一段时间,包括公司注册用户、相关讨论共享等重要资料,需要将其移植到新安装TFS服务器的机器上,并对外公开访问权限。旧的机器上安装的是SQL
Server 2000,在将论坛数据库备份之后,在SQL Server
2008R2的数据库上还原的过程中,出现了如下的3145错误:

利用T-SQL语句,实现数据库的备份与还原的功能体现了SQL
Server中的四个知识点:

我们来还原损坏的页

现在你是DBA,你想恢复这个数据库到正确状态且不丢失数据(像我们在最后一步插入的记录)。你会怎么做?首先你要进行所谓的尾日志备份(Tail-Log
Backup)
:你要备份自上次事务日志备份后的已发生的事务。

1 -- Backup the transaction log2 BACKUP LOG PageLevelRestores TO3   DISK = 'C:\Backups\PageLevelRestores_LOG1.bak'4   WITH INIT5 GO

在这里还没有进行过事务日志备份,因此我们的备份会包含自完整备份后,所有已执行的事务。现在我们可以在SQL
Server里初始页级别还原操作。这里你使用传统的RESTORE
DATABASE
的T-SQL命令,但你只要指定想要还原的页,不用还原整个数据库,我们只要还原有问题的页。如果你在处理很大的数据库,这会有很大的区别。

1 USE master2 -- Restore full database backup3 RESTORE DATABASE PageLevelRestores4   PAGE = '1:90'5   FROM DISK = 'C:\Backups\PageLevelRestores.bak'6   WITH NORECOVERY7 GO

现在到了棘手的部分:在RESTORE
DATABASE
的T-SQL命令后,你要进行另一个事务日志备份。这个另外的日志备份是需要的,因为接下来你要保证在这个可用页进行的所有改变用作还原。没有这个额外的日志备份,SQL
Server不能把你的页重新上线。

1 -- Backup the tail of the log...2 BACKUP LOG PageLevelRestores TO3   DISK = 'C:\Backups\PageLevelRestores_LOG_TAIL.bak'4   WITH INIT5 GO

进行完这个额外日志备份后,最后你可以在正确的顺序里恢复所有的日志备份,最后你可以把数据库恢复上线。

 1 -- Restore all available log backups in the correct order 2 RESTORE LOG PageLevelRestores FROM 3   DISK = 'C:\Backups\PageLevelRestores_LOG1.bak' 4   WITH NORECOVERY 5 GO 6  7 -- Finally restore the tail log backup 8 RESTORE LOG PageLevelRestores FROM 9   DISK = 'C:\Backups\PageLevelRestores_LOG_TAIL.bak'10   WITH NORECOVERY11 GO12 13 -- Finally finish with the restore sequence14 RESTORE DATABASE PageLevelRestores WITH RECOVERY15 GO

现在当你再次查询表时,你会看到SELECT语句成功执行没有任何I/O错误,在这个表里没有丢失任何数据。还是很简单,是不是?

1 USE PageLevelRestores2 GO3 4 -- Retrieve the selected records5 SELECT * FROM Test6 GO

图片 5

  (1)还原操作中的数据库名称与备份集中记录的数据库名称不匹配。

备份集中的数据库备份与现有的 ‘xxx’ 数据库不同。

1. 获取SQL Server服务器上的默认目录

小结

在SQL
Server里如何进行页级别恢复操作是每个DBA应该知道的。它是你工具箱里最重要的工具之一——尤其当你在处理很大的数据库时。不用恢复整个数据库,你只恢复有问题的页,整个恢复过程会非常快。

最后给你一个问题,各位看官:你是否有过在SQL
Server进行页级别的恢复,如果是的话——对你来说它有多难/简单?请畅所欲言!

感谢关注!

  (2)需要通过还原操作自动创建一个或多个文件,但已有同名的文件存在。

一直以为SQL
Server数据库是向前后兼容的,怎么可能存在这样的问题呢?不过检查一番之后,确定两台数据库服务器都没有问题,且论坛的备份在原数据库服务器上可以正常还原。没办法,改之,把在SQL
Server 2008R2上新建的数据库更改为 SQL Server
2000格式的,再尝试还原,仍然是同样的错误。在网上找了下,说到的办法,尝试了不少,其中有一篇是“备份集中的数据库备份与现有的数据库不同
SQL 2005 ERROR:3145
解决办法
”,提供了如下命令方法:

2. 备份SQL语句的使用

 
(3)还原操作中命名的数据库已在服务器上,但是与数据库备份中包含的数据库不是同一个数据库,例如数据库名称虽相同,但是数据库的创建方式不同。

1.新建一个与现有数据库重名的数据库

2.在查询窗口运行如下语句:

use master
restore database AdventureWorks from disk =
‘E:\databasebackup\AdventureWorks.bak’
with replace, MOVE N’AdventureWorks_Data’ TO N’C:\Program
Files\Microsoft SQL
Server\MSSQL.5\MSSQL\Data\AdventureWorks.mdf’, MOVE
N’AdventureWorks_Log’ TO N’C:\Program Files\Microsoft SQL
Server\MSSQL.5\MSSQL\Data\AdventureWorks_log.ldf’

从语句上来看,和在图形界面的操作是一样的,但是结果却是语句运行成功,图形界面怎么过不去。

3. 恢复SQL语句的使用,同时考虑了强制恢复时关闭其他用户进程的处理

   如果重新创建一个数据库,可以禁止这些安全检查。

在其他网页上也看到了类似的方法,也有不少成功解决这个错误的,不过,在SQL
Server 2008R2服务器上,还是无法正确备份,也没有找到有其它人在SQL Server
2008R2上,用其它方法解决还原备份的问题。

4. 作业创建SQL语句的使用

一。数据库恢复模型

不过,将论坛数据库从旧的SQL Server 2000上分离出来,再附加上SQL Server
2008R2上,成功,数据完整的恢复了!

<

根据保存数据的需要和对存储介质使用的考虑,SQL
Server提供了3种数据库恢复模型:简单恢复、完全恢复、大容量日志记录恢复。

不清楚为什么在SQL Server 2005上可以,在SQL Server
2008R2却不能恢复备份。不过,好在使用分离和附加的方法,成功将论坛迁移。在此记下,做为参考吧。

1.简单恢复模型

 
简单恢复模型可以将数据库恢复到上次备份处,但是无法将数据库还原到故障点或待定的即时点。它常用于恢复最新的完整数据库备份、差异备份。

 简单恢复模型的有点是允许高性能大容量复制操作,以及可以回收日志空间。但是必须重组最新的数据库或者差异备份后的更改。

2.完全恢复模型

 
完全恢复模型使用数据库备份和事务日志备份提供将数据库恢复到故障点或特定即时点的能力。为保证这种恢复程度,包括大容量操作(如SELECT
INTO、CREATE INDEX和大容量装载数据)在内的所有操作都将完整地记入日志。

完全恢复模型的优点是可以恢复到任意即时点,这样数据文件的丢失和损坏不会导致工作损失,但是如果事务日志损坏,则必须重新做最新的日志备份后进行修改。

3.大容量日志记录恢复模型

 
大容量日志记录恢复模型为某些大规模或大容量复制操作提供最佳性能和最少日志使用空间。在这种模型中,大容量复制操作的数据丢失程度要比完全恢复模型严重,因为在这种模型下,只记录操作的最小日志,无法逐个控制这些操作。它只允许数据库恢复到事务日志备份的结尾处,不支持即时点恢复。

 
大容量日志记录恢复模型的优点是可以节省日志空间,但是如果日志损坏或者日志备份后发生了大容量操作,则必须重做自上次备份后所做的更改。

 不同的hi付模型针对不同的性能、磁盘和磁带空间以及保护数据丢失的需要。恢复模型决定总体备份策略,包括可以使用的备份类型,即选择一种恢复模型,可以确定如何备份数据以及能承受何种程度的数据丢失,由此确定了数据的恢复过程。

二。查看备份信息

由于恢复数据库与备份数据库之间往往存在较长的时间差,难以记住备份设备和备份文件及其所备份的数据库,需要对这些信息进行查看。

 
需要查看的信息通常包括:备份集内的数据和日志文件、备份首部信息、介质首部信息。可以使用SQL
Server管理平台和Transact-SQL语句查看这些信息。

 1.使用SQL Server管理平台查看备份信息

  使用SQL Server查看所有备份介质属性的操作步骤如下:

  (1) 打开SQL
Server管理平台,在对象资源管理器中,展开结点”服务器树“→”服务器对象“→”备份设备“,右击某个具体备份设备名称,在弹出的快捷菜单上选择”属性“命令,打开”备份设备“属性窗口。

(2)在”备份设备“属性窗口选择”媒体内容“选择卡,打开的窗口,在列表框中列出所选备份媒体的有关信息。

2.使用Transact-SQL语句查看备份信息

 RESTORE HEADERONLY语句的格式为:

RESTORE HEADERONLY

留下评论

网站地图xml地图