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

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

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

转贴路径:

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

我们来破坏一个页

第一步我想向你展示下如何建立表(或索引)里有个特定页损坏的情景,这里我们会进行一些魔术,因为开箱即用(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 8kb
11 CREATE TABLE Test
12 (
13     Filler CHAR(8000)
14 )
15 GO
16 
17 -- Insert 4 records
18 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 GO
23 
24 -- Retrieve the selected records
25 SELECT * FROM Test
26 GO

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

1 -- Perform a full database backup
2 BACKUP DATABASE PageLevelRestores TO DISK = N'C:\Backups\PageLevelRestores.bak'
3 GO
4 
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_USER
11 GO

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

1 -- Retrieve the selected records
2 SELECT * FROM Test
3 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

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

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

我们来还原损坏的页

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

1 -- Backup the transaction log
2 BACKUP LOG PageLevelRestores TO
3     DISK = 'C:\Backups\PageLevelRestores_LOG1.bak'
4     WITH INIT
5 GO

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

1 USE master
2 -- Restore full database backup
3 RESTORE DATABASE PageLevelRestores
4     PAGE = '1:90'
5     FROM DISK = 'C:\Backups\PageLevelRestores.bak'
6     WITH NORECOVERY
7 GO

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

1 -- Backup the tail of the log...
2 BACKUP LOG PageLevelRestores TO
3     DISK = 'C:\Backups\PageLevelRestores_LOG_TAIL.bak'
4     WITH INIT
5 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 NORECOVERY
11 GO
12 
13 -- Finally finish with the restore sequence
14 RESTORE DATABASE PageLevelRestores WITH RECOVERY
15 GO

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

1 USE PageLevelRestores
2 GO
3 
4 -- Retrieve the selected records
5 SELECT * FROM Test
6 GO

图片 5

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

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

小结

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

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

感谢关注!

2. 备份SQL语句的使用

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

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

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

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

一。数据库恢复模型

<

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

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

FROM <backpi_device>

[WITH {NOUNLOAD|UNLOAD}

[[,]FILE =file_number]

[[,]PASSWORD={password|@password_var}]

[[,]MEDIAPASSWORD={mediapassword|@mediapassword_var}]

<backup_device>::={

{‘logical_backup_device_name’|@logical_backup_device_name_var}

|{DISK|TAPE}={‘physical_backup_device_name’|@physical_backup_name_var}

}]

相关文章