SQLServer2008备份和恢复

最新推荐文章于 2023-02-10 13:59:45 发布

weixin_33859504

最新推荐文章于 2023-02-10 13:59:45 发布

阅读量307

点赞数

原文链接：http://blog.51cto.com/yangkui/2046367

版权

目标：
1.理解SQLServer2008R2的备份方式(完整，差异，日志备份)和恢复模式;
2.能够进行完整备份，差异备份操作,事务日志备份，并进行恢复操作;
3.使用维护计划实现日常的数据库备份操作;

一、SQLServer2008R2的备份恢复

1、恢复模式类型

所有的数据库都可以设置为三个不同的恢复模式：简单(simple)，完全(full)，大容量日志(Bulk-Logged).

A完全恢复模式
完全恢复模式是默认的恢复模式。在完全恢复模式下，需要手工的对事务日志进行管理。使用完全恢复模式的优点是可以恢复到数据库失败或者指定的时间点上。缺点则是，如果没有进行管理的话，事务日志将会快速增长，消耗磁盘空间。要清除事务日志，只能通过备份事务日志，或者切换至简单模式。
B简单恢复模式
与完全恢复模式不同的是，在简单恢复模式下，在检查点发生时（checkpoint），当前已被提交的事务日志将会被清除。
因此，在简单恢复模式下，容易造成数据丢失，因为无法将数据库恢复到失败的那一刻。需要注意的是，虽然在简单恢复模式下，系统会自动定期清除日志，但这并不意味着事务日志文件不会增长。例如，如果执行一个批量插入操作时，SQL SERVER会将该相关操作当成一个事务，期间产生的日志量在极端情况下，还是非常可观的。

C大容量日志恢复模式

大容量日志恢复模式与完全恢复模式非常相似，但与完全恢复模式不同的是，批量操作将会尽量被最少记录。

批量操作有以下几种类型：

批量导入数据，例如使用BCP(Bulk Copy Import) ， BULK INSERT命令，或者是在BULK使用OPENROWSET命令；
大对象操作(LOB),例如在TEXT, NTEXT, IMAGE 列上使用WRITETEXT 或者UPDATETEXT；
SELECT INTO 字句；
CREATE INDEX, ALTER INDEX, ALTER INDEX REBUILD, DBCC REINDEX
在完全恢复模式下，上述操作产生的日志将会是非常大的。而使用大容量日志恢复模式将会阻止不需要或者非预期的日志增长。
在批量操作发生时，SQL SERVER仅仅记录了相关数据页(data page)的ID，在SQL SERVER中，SQL SERVER pages 都有内部 ID，如5：547。用这种方式，能够将大量的page ID记录在小的日志文件里。
使用大容量日志恢复模式，将会使数据仓库或者有大批量操作的数据库减少很大的空间。但使用大容量恢复模式时，会使得恢复变得比较困难，一般来说，只能恢复到最后的事务日志备份点上，但如果所有的事务日志都被备份后，还是可以恢复成功的。
2、改变恢复模式
改变数据库的恢复模式，可以通过以下语句来实现：
ALTER DATABASE database_name SET RECOVEY BULK_LOGGED
改变恢复模式并不需要重启数据库实例。
二、数据库备份介绍
1、备份位置
在SQL Server上，有多种备份位置可以选择，如本地磁盘，网络磁盘，远程地址，磁带等。各种备份位置均有自己的优点和缺点。
2、逻辑备份设备
在SQL Server上，可以通过创建逻辑备份设备来完成备份。使用逻辑备份设备的好处是，当变更备份地址时，不需要更改备份脚本，只需要更改逻辑备份设备的定义即可。

创建逻辑备份设备的脚本如下：
SQL code
EXEC sp_adddumpdevice @devtype=’disk’,@logicalname=’MYBackup’,@physicalname=’D:\backup\mydb.bak’
删除备份设备的脚本：
SQL code
Sp_dropdevice @logicalname=’MYBackup’
上述脚本只是删除逻辑备份设备的定义，下述脚本将同时删除备份文件：
SQL code
Sp_dropdevice @logicalname=’MYBackup’,@devfile=’DELFILE’

使用逻辑备份设备的方法如下：
SQL code
Backup database mydb to MYBackup
当然，还可在逻辑备份设备上指定过期时间等备份属性，如：
SQL code
Backup database mydb to MYBackup WITH EXPIREDATE=’13/01/2010’
或：
SQL code
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7

3、备份集与存储集

每一份备份包含于一个备份集，而一个备份集包含于一个存储集。通过系统GUI进行备份时，SQL Server会自动指定备份集和存储集，目的则是为了简化管理。用T-SQL显示指定则用如下语法：
SQL code
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7,
NAME=’FULL’,MEDIANAME=’ALLBackups’
NAMEs是指备份集名称，MEDIANAME是指存储集名称。

4、全备份
不管恢复模式是哪一个，所有的备份都必须要有一个全备份，特别是日志备份和差异备份，如果没有全备份的话，将无法进行恢复。
简单的全备份脚本如下所示，也可以通过维护计划来指定全备份：
SQL code
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’
但需要注意的是，上述命令是将数据库备份附加到当前的存在的文件上，如果不存在则创建它，并不会覆盖原有文件。要覆盖同名的备份文件，需要指定INIT参数。
SQL code
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’ WITH INIT

5、日志备份
在完全恢复模式或者大容量日志恢复模式下，日志备份不仅仅是恢复的需要，同时也是手工管理事务日志文件的一种方式。如果从不进行备份的话，在完全恢复模式或者大容量恢复模式下，事务日志将会持续增长，直至消耗完所在磁盘。
日志备份的脚本如下：
SQL code
BACKUP LOG mydb_log TO DISK=’D:\backup\mydb.trn’
需要养成使用.trn为日志备份的扩展名的习惯。

每个在数据库上的动作都会被安排一个Log Sequence Number (LSN)。如果需要还原到指定的时间点，需要有持续的LSN记录。也就是说，在完全恢复模式或者大容量日志模式下，一个不被打断的事务日志备份链是恢复数据库的基本要求。

6、差异备份
使用日志备份来恢复时，无疑是一个很慢的过程，特别是上一个全备份的历史比较悠久时。使用差异备份，便能缩短恢复时间。事实上，差异备份只是BACKUP DATABASE的一个选项，如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\backup\mydb.dif’ WITH DIFFERENTIAL,INIT

进行数据库恢复时，先恢复数据库全备份，再恢复数据库差异备份，最后才恢复日志备份。差异备份是与上一次全备份紧密相连的，不管期间有多少次日志备份和差异备份，差异备份还是会从上一次全备开始备份。因此，经常会遇到这样的一种情况，在生产库上需要临时使用数据库时，便用BACKUP DATABASE … TO DISK=’..’进行了一个备份，下一次的差异备份便会以这回的全备为准，如果过后把这个临时全备删除掉后，后面的差异备份就没用了。差异备份并不意味着磁盘空间肯定会少，这取决于实际情况。当期间大量操作发生时，差异备份还是会变得很大。
7、错误检测

在备份过程中，备份进程会同时验证数据，或者校验不完整页（torn page），或者验证校验和(checksum)。要使用该功能，需要激活该选项。
不完整页检测(Torn-page dection)仅仅检查每一个页看是否已经写完成。如果发现一个页只有部分被写入，那么就将其标记为torn。
校验和验证(checksum validation)是一种新的页验证机制。它会为每个页添加一个值来表明该页实际的大小。虽然看起来是个代价很高影响性能的操作，但事实上，它的效率非常高，与torn-page差不多。
备份进程在备份数据库时，会通过比较在数据库里的和随着备份页写入硬盘时这两个之间的值来进行验证。但是，这个验证并不是自动完成的，需要显示指定，在GUI页面上是个选项。如果通过T-SQL来备份的话，语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\data\mydb.bak’ WITH CHECKSUM

如果备份过程中，发现了错误，SQL Server会错误信息写入MSDB上的SUSPECT_PAGE表里面。同时，在默认情况下，备份行为会停止的（STOP_ON_ERROR），以便管理员排查错误。
但备份过程中的校验和验证还有另外一个选项（CONTINUE_ON_ERROR）,也就是说，如果发现错误，备份过程并不会中断，而是将错误页信息记录在 MSDB..SUSPECT_PAGE上而已。需要注意的是，SUSPECT_PAGE表是有行限制的，最多只能达到1000行，如果达到了的话，备份同样会失败。激活校验和验证的话，很明显会影响备份的性能。但还是很有必要的。

8、安全备份
完全备份和日志备份语句还支持使用密码属性，如：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’ WITH PASSWORD=’mydb’

所指定的密码是很容易破解的。因此，如果确实需要对某些备份数据进行加密的话，可以将备份存放于加密的文件系统或者其它安全的存储设备上。
同时，SQL Server还提供了对真实列进行加密的功能。该加密功能是工业标准。
9、条带备份
有些情况下，单独一个硬盘无法存储一个完整的数据库备份时，可以将数据库备份分成多个部分存储在不同的磁盘上，这种备份方式成为条带备份。使用条带备份的优点很明确，就是能很好的利用空间，但如果某部分备份丢失或者损坏，那整个备份将无效。
其语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’,DISK=’E:\mydb.bak’ WITH INIT,CHECKSUM, CONTINUE_ON_ERROR
上述D盘和E盘上的备份是不可分割的。

10、镜像备份

与条带备份在多个磁盘上保留同一份备份不同的是，镜像备份是在不同磁盘上保留多份备份。其语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’
MIRROR TO DISK=’E:\mydb.bak’
WITH INIT,CHECKSUM,CONTINUE_ON_ERROR

在实际情况下，对日志备份采取镜像备份方式会比较合适。

11、COPY-ONLY 备份
在差异备份里曾提到过，差异备份是建立在上一个全备份的基础上的。因此如果在一个事先安排好的备份计划里，如果在全备份和差异备份之间再进行了一次全备份后，其差异备份会被打断，如果把临时全备份删除掉后，就产生了数据丢失。在SQL SERVER 2005以后，SQL SERVER提供了一个选项copy-only.使用copy-only选项进行的全备份便不会打算原先的备份计划，语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’ WITH INIT,CHECKSUM,COPY_ONLY

三、文件和文件组备份

1、备份数据文件
备份数据文件同样可以通过BACKUP DATABASE语句来实现。如下：
SQL code
BACKUP DATABASE mydb FILE=’D:\Data\mydb.ndf’ TO DISK=’E:\Backup\mydbdata.bak’
上述语句相当于数据文件级别的全备份，与数据库级别的备份类似，文件级别上的备份也有差异备份，当然前提是要有相对应的文件全备份。
差异备份的语句如下：
SQL code
BACKUP DATABASE mydb FILE=’D:\Data\mydb.ndf’ WITH DIFFERENTIAL
TO DISK=’E:\Backup\mydbdata_dif.bak’

5、备份文件组

与单独备份文件类似，也可以对文件组进行类似的备份操作。备份文件组的方式也有两种，一种是通过GUI界面指定，一种则是通过T-SQL。

T-SQL的语句如下：
SQL code
BACKUP DATABASE mydb FILEGROUP=’PRIMARY’ TO DISK=’E:\Backup\mydbpri.bak’

6、不完全备份(partial backup)

在文件组备份上，不完全备份其实相当于完全备份，可以通过指定关键字READ_WRITE_FILEGROUPS来实现不完全备份。

语句如下：
SQL code
BACKUP DATABASE mydb READ_WRITE_FILEGROUPS TO DISK=’D:\mydb.bak’

那不完全备份到底是什么意思呢？什么时候需要不完全备份？如果对一个文件组设置了只读，而这只读的文件组又需要进行一次备份，这时，可以不用BACKUP DATABASE语句进行备份，只需要挑个时间停止实例，然后执行不完全备份。

四、数据恢复

1、Restore vs. Recovery

Restore和Recovery是两个不同的概念，但在数据恢复过程中又是紧密联系的。

Restore相当于从备份集中重建整个或者部分数据库，Restore是无法改变数据库状态的，如脱机和联机等。

一旦对数据库进行了Recovery，则将无法再进行Restore操作。

数据库恢复的语句如下：
SQL code
RESTORE DATABASE mydb FROM mydbdevice WITH RECOVERY

显示指定RECOVERY 或者NORECOVERY是个良好的习惯。

2、备份文件里面的信息

在primary文件里，存储着与数据库结构有关的一些信息，如文件位置等。因此备份后，这些信息同样保留在备份文件里面。如果要恢复到不同的磁盘上或服务器后，需要进行额外的更改。

在SQL Server里，提供了 RESTORE HEADERONLY, RESTORE FILELISTONLY, RESTORE LABELONLY, RESTORE VERIFYONLY 等命令来读取备份文件的信息。也可以通过MSDB里的表来获取备份集的相关信息。
A．RESTORE HEADERONLY

SQL code
RESTORE HEADERONLY
FROM DISK='D:\family_20100108.bak'

各字段的含义可以查看联机丛书。
B.RESOTRE FILELISTONLY
SQL code
RESTORE FILELISTONLY
FROM DISK='D:\family_20100108.bak'

C.RESOTRE LABELONLY
SQL code
RESTORE LABELONLY
FROM DISK='D:\family_20100108.bak'

D.RESOTORE VERIFYONLY
SQL code
RESTORE VERIFYONLY
FROM DISK='D:\family_20100108.bak'

3、从全备份中恢复

需要注意的是，在做恢复之前，应该养成对当前日志进行备份的习惯，否则容易造成数据丢失。SQL Server虽然提供REPLACE选项以便强制恢复，但这样子末尾日志就丢掉了。
通常，如果未对当前日志进行备份，那么会收到如下错误
上述错误，提到了使用WITH REPLACE 或者WITH STOPAT命令来完成恢复，但应尽量避免使用这两个命令。

如果要将数据库恢复到不同的磁盘上，可以通过GUI页面指定，也可以通过T-SQL语句来实现。

T-SQL实现的方式如下：
SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak'
WITH MOVE 'Family'TO 'D:\Family.mdf',
MOVE 'Family_Log' TO 'D:\Family.ldf'
WITH RECOVERY

4、恢复到指定的时间点

要恢复到指定的时间点有三种选择，一种是通过明确指定时间，一种通过指定LSN号，另外一种则是通过创建和指定log marks。
A. TIME

通常情况下，恢复都会有要求恢复到指定时间点的要求，可通过GUI界面来实现，也可以通过T-SQL 来实现
SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak'
WITH NORECOVERY

RESTORE LOG Family
FROM DISK='D:\family_20100108.trn'
WITH RECOVERY,STOPAT 'jan 8,2009 3:10pm'

B. LSN

如果知道确切的LSN号，也可以通过LSN号来恢复指定的LSN。获取LSN相关信息，可以通过RESOTRE HEADERONLY。这种方式只能通过T-SQL来实现。

SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak'
WITH NORECOVRY

RESTORE LOG Family
FROM DISK='D:\family_20100108.trn'
WITH RECOVRY,STOPATMARK LSN:2433:5422

C. Log Marks
也可以通过创建Log Mark，可以恢复至指定的Log Marks。例如，创建了一个logmarkexample，则在恢复时，恢复到logmarkexample。

SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak'
WITH NORECOVRY

RESTORE LOG Family
FROM DISK='D:\family_20100108.trn'
WITH RECOVRY,STOPATMARK 'logmarkexample'

5、对镜像备份或条带备份的恢复

对镜像备份而言，每一份备份都是一样的，因此恢复任何一份备份都可以完成恢复。对条带备份而言，则需要同时指定所有的条带备份，这种备份可以比单独一个备份来得快。

条带备份例子如下：
SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak',
DISK='D:\family_20100108.bak'
WITH NORECOVRY

6、恢复数据页

在SQL Server2005以后，SQL Server提供了对数据页恢复的功能。对数据页恢复可以在联机或者脱机状态下进行
但只能对实际用户数据页进行恢复，而其他的页则无法通过备份来恢复。如Global Allocation Map(GAM), Secondary Global Allocation Map(SGAM), Page Free Space(PFS)等。
恢复数据页相当于进行完全恢复，不同的是需要指定具体的页面。
SQL code
RESTORE DATABASE Family PAGE '20:1570,20:1571,20:1572'
FROM DISK='D:\family_20100108.bak'
WITH NORECOVRY

数据页可以通过MSDB..SUSPECT_PAGE或者DBCC CHECKDB来查找。

7、对系统数据库的恢复

系统数据库存储着一个SQL Server实例上相关数据库的信息，如果丢失，将会带来更大的损失。
A. MASTER
Master数据库的恢复与其他数据库的恢复是不同的。要恢复MASTER数据库，需要从将SQL Server切换至单用户模式，如果无法切换，则停止SQL Server服务，然后用sqlserver –m命令行启动。
当然，也可以用net start “服务器名” 来启动SQL Server服务。启动后，再用SQLCMD命令进行还原

B. MSDB
在MSDB里面存储得比较多的是SQL Agent里的内容，如作业，调度，操作员，警告等信息；同时还存放SQL Server Integration Service（SSIS）等信息。
其恢复过程与普通数据库恢复过程是一样的。并且由于是在简单模式下，因而其恢复过程更加简单。

C. MODEL

Model数据库用来存放创建数据库时需要的信息，如果有使用MODEL数据库的话，也需要对其进行备份和恢复。
备份与恢复的过程与普通数据库一致。
D. Tempdb

Tempdb是不需要备份和恢复的，在每次的启动过程中，SQL Server会自动清除tempdb，并重新启动tempdb。在tempdb上需要注意的是其空间规划，因为某些情况下tempdb会变得非常大，耗尽空间，最终导致SQL Server关掉。

若要修改tempdb的存储路径，请使用如下语句：

SQL code
use master

Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')

Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')

E. Resource

Resource是SQL Server2005以后新引进的一个数据库，将以前存放于master等其他系统数据库的部分信息存放于Resource数据库里。

对Resource不能通过T-SQL或者GUI备份，因为看不到它，要对其进行备份，只能通过手工直接拷贝其物理文件。

6、数据文件备份的恢复

在第三部份里面提到了文件的备份，这种单个或多个文件的备份，其恢复方式与数据库恢复类似。但要养成一个良好的习惯，在恢复前，备份当前的日志文件。

SQL code
BACKUP LOG Family
TO DISK='E:\Familylog.bak'
WITH NORECOVERY

接着对需要还原的文件进行还原，如

SQL code
RESTORE DATABASE Family
FILE='D:\DATA\Family.mdf'
FROM DISK='E:\Familyprimary.bak'
WITH NORECOVERY

RESTORE LOG Family
FROM DISK='E:\Familylog.bak'
WITH NORECOVERY

RESTORE DATABASE Family
WITH RECOVERY

如果在数据文件上还有差异备份，日志恢复前进行差异备份恢复，如：
SQL code
RESTORE DATABASE Family
FILE='D:\DATA\Family.mdf'
FROM DISK='E:\Familyprimary.bak'
WITH NORECOVERY

RESTORE DATABASE Family
FILE='D:\DATA\Family.mdf'
FROM DISK='E:\Familyprimay.dif'
WITH NORECOVERY

RESTORE LOG Family
FROM DISK='E:\Familylog.bak'
WITH NORECOVERY

RESTORE DATABASE Family
WITH RECOVERY

7、文件组的恢复
与数据文件恢复原理是一致的，只不过是将具体文件文件组。
SQL code
BACKUP LOG Family
TO DISK='E:\Familylog.bak'
WITH NORECOVERY

RESTORE DATABASE Family
FILEGROUP='PRIMARY'
FROM DISK='E:\Familyprimary.bak'
WITH NORECOVERY

RESTORE LOG Family
FROM DISK='E:\Familylog.bak'
WITH NORECOVERY

RESTORE DATABASE Family
WITH RECOVERY

定时任务：
◆1、管理->SQL Server代理->作业(按鼠标右键)->新建作业->
◆2、新建作业属性(常规)->名称[自定义本次作业的名称]->启用的方框内是勾号->
分类处可选择也可用默认的[未分类(本地)]->所有者默认为登录SQL Server用户[也可选其它的登录]->
描述[填写本次工作详细描述内容]；
[ 创建作业分类的步骤:
SQL Server代理->作业->右键选所有任务->添加、修改、删除 ]
◆3、新建作业属性(步骤)->新建->步骤名[自定义第一步骤名称]->类型[Transact-SQL(TSQL)脚本]->
数据库[要操作的数据库]->命令
[如果是简单的SQL直接写进去即可，也可用打开按钮输入一个已写好的*.sql文件
如果要执行存储过程，填
exec p_procedure_name v_parameter1,[ v_parameter2…v_parameterN]
->确定
(如果有多个步骤，可以再次调用下面的新建按钮；也可以对已有的多个步骤插入、编辑、删除)；
◆4、建作业属性(调度)->新建调度->名称[自定义调度名称]->启用的方框内是勾号->调度->反复出现->
更改[调度时间表]->确定
(如果只要保存此作业，不要定时做可以把启用的方框内是勾号去掉)；
◆5、建作业属性(通知)->用默认的通知方法就好[当作业失败时，写入Windows应用程序系统日志] ->确定。
跟作业执行相关的一些SQL Server知识：
SQLSERVERAGENT服务必须正常运行，启动它的NT登录用户要跟启动SQL Server数据库的NT登录用户一致。
点作业右键可以查看作业执行的历史记录情况，也可以立即启动作业和停止作业。

转载于:https://blog.51cto.com/yangkui/2046367

weixin_33859504

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQLServer2008备份和恢复

目标：1.理解SQLServer2008R2的备份方式(完整，差异，日志备份)和恢复模式;2.能够进行完整备份，差异备份操作,事务日志备份，并进行恢复操作;3.使用维护计划实现日常的数据库备份操作;一、SQLServer2008R2的备份恢复1、恢复模式类型所有的数据库都可以设置为三个不同的恢复模式：简单(simple)，完全(full)，大容量日志(Bulk-Logged).A完全恢复...
复制链接

扫一扫