2022年数据库的备份与恢复可用 .pdf

《2022年数据库的备份与恢复可用 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库的备份与恢复可用 .pdf（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库的备份与恢复（一）收藏其实里面没有什么新的内容，主要就是这两天翻了邹老大的第一本书里面的数据库备份与恢复一章， 把里面的内容自己稍微总结一下，所以里面的主要内容主要还是邹老大的内容。如果邹老大认为有侵权行为，告诉我，我删掉这个blog 。一、数据库备份的与还原的四种发放1、 完全备份：包含了指定数据库中的所有数据，生成的备份文件最大，需要处理的时间也最长。2、 差异备份：指备份自上次完全备份，发生了更改的数据。在做差异备份前，必须至少有一次完全备份。3、 事务日志备份：之备份自上次备份后对数据库执行的所有事务的一系列记录。这个上次的备份，可以是完全备份、差异备份或者日志备份。日志备份前，

2、至少要有一次完全备份。4、 文件和文件组备份：可以仅备份数据库中的某个特定数据文件或文件组，使用于把经常变化和不经常变化的数据分配到不同的文件组。在一般进行备份时，会发现事务日志备份与文件和文件组备份不可用。那是因为在Sql Server 2000中默认的恢复模型为简单恢复模型，是不允许使用事务日志备份、 文件和文件组备份。只要吧恢复模型更改为完全或者大容量日志记录恢复模型后，这两种备份方式就可以使用了。 参见下图。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 9 页

3、- - - - - - - - - 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 9 页 - - - - - - - - - 注意：刚修改后，在备份模型这不能马上显示出来，需要等待一会方能显示出来。（心急是吃不了热豆腐的）二、Master 数据库的备份与还原在 SQL Server2000 中，master 数据库记录了整个系统级别的信息，还记录了所有的登录账户和系统配置设置。所以当创建或者删除用户数据库、 添加登陆或者执行其他与登录安全有关的操作，创建或者删除备份设备

4、等应该对master 数据库进行备份。1、备份：对于 master 数据库的备份与普通数据库相同。Backup database master to disk=d:master.bak2、恢复： master 数据库的还原相对来说比较麻烦。 1）、master 数据库损坏，那么需要重建master 数据库，在 microsoft sql server80 、toolsbinn下运行 rebuildm.exe ，然后指定服务器名与数据文件目录 2）、master 数据库没有损坏，那么恢复的时候，先要切换到单用户模式下启动server服务。首先，先停止mssqlserver 服务；第二，在命令模式

5、下，运行sqlservr.exe c m -instancename，如果数据库使用的默认的实例名，那么就不用写实例名了。如果有实例名，那么实例名一定要写上， 否则单用户启动模式启动不成功。第三，在查询分析器中运行恢复数据库的名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 9 页 - - - - - - - - - 语句， restore databse master from disk =d:master.bak。在登陆查询分析器的时候，会提示登陆失败，在这里不用管这个

6、错误即可，单击否即可。三、用户数据库的备份与恢复1、使用企业管理器： 服务器组 服务器 数据库 所有任务 所有任务 备份或者恢复数据库。在备份的时候需要先添加备份设备或者备份文件2、完全备份： Backup database pubs to disk=完全备份 .bak 恢复： Restore database pubs from disk=完全备份 .bak 3、差异备份： Backup database pubs to disk=差异备份 .bak with differential恢复： 1）进行完全恢复：restore database pubs from disk=完全备份 .bak

7、 with norecovery 2）进行差异恢复： restore database pubs from disk=差异备份 .bak with recovery在进行第一步恢复的时候一定要指定with norecovery，否则会提示“服务器 : 消息 4306 ，级别 16 ，状态 1 ，行 1先前的还原操作未指定 WITH NORECOVERY 或 WITH STANDBY。请在除最后步骤之外的所有其它步骤中指定 WITH NORECOVERY 或 WITH STANDBY 后，重新启动该还原序列。服务器 : 消息 3013 ，级别 16 ，状态 1 ，行 1RESTORE DATAB

8、ASE 操作异常终止。“错误。4、日志备份： Backup log pubs to disk=日志备份 .bak 还原： 1）先进行完全恢复：restore database pubs from disk=完全备份 .bak with norecovery 2）进行差异恢复： restore database pubs from disk=差异备份 .bak with norecovery 3）进行日志恢复，按照时间顺序（日志备份的顺序）来进行恢复： Restor log pubs from disk=日志备份 .bak with recovery5、数据文件或文件组备份与还原- 创建测试数据

9、库，使用的是邹老大的代码CREATE DATABASE dbON PRIMARY( NAME=db_data, FILENAME= c:db_data.mdf),FILEGROUP db_fg1( NAME = db_fg1_data, FILENAME = c:db_fg1_data.ndf),FILEGROUP db_fg2( NAME = db_fg2_data, FILENAME = c:db_fg2_data.ndf)LOG ON(名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - -

10、 第 4 页，共 9 页 - - - - - - - - - NAME=db_log, FILENAME =c:db.ldf)GO- 在文件组 db_fg1 上创建表，并单独创建该文件组的备份CREATE TABLE db.dbo.tb(id int) ON db_fg1BACKUP DATABASE db FILEGROUP=db_fg1 TO DISK=c:db_fg1.bak WITH FORMATGO在其他文件组上创建表CREATE TABLE db.dbo.ta(id int) ON PRIMARYCREATE TABLE db.dbo.tc(id int) ON db_fg2INS

11、ERT db.dbo.tb SELECT id FROM sysobjects- 备份每个文件组，并且备份事务日志BACKUP DATABASE db FILEGROUP=PRIMARY TO DISK=c:db_primary.bak WITH FORMATBACKUP DATABASE db FILEGROUP=db_fg1 TO DISK=c:db_fg1_new.bak WITH FORMATBACKUP DATABASE db FILEGROUP=db_fg2 TO DISK=c:db_fg2.bak WITH FORMATBACKUP LOG db TO DISK=c:db_log

12、.bak WITH FORMATGO- 删除数据库DROP DATABASE dbGO- 从文件组备份中恢复数据RESTORE DATABASE db FILEGROUP=PRIMARY FROM DISK=c:db_primary.bak WITH NORECOVERY,REPLACERESTORE DATABASE db FILEGROUP=db_fg1 FROM DISK=c:db_fg1.bak WITH NORECOVERYRESTORE DATABASE db FILEGROUP=db_fg2 FROM DISK=c:db_fg2.bak WITH NORECOVERYRESTOR

13、E LOG db FROM DISK=c:db_log.bak WITH RECOVERYSELECT COUNT(*) FROM db.dbo.tbGO- 删除测试数据库DROP DATABASE db数据库备份与恢复（二）收藏四、数据库的迁移名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 9 页 - - - - - - - - - 当需要把数据库从一个服务器迁移到另一个服务器的时候，需要从以下几个方面进行准备：1、备份系统数据库，master 、mode 、msdb

14、。对 master 数据库进行备份是因为master 数据库中系统的登陆信息，如果不对 master 进行备份， 那么在新的服务器上不对master 数据库进行恢复的话，有可能造成孤立用户。2、记录原服务器上SQL Server 的排序规则。通过 select serverproperty(collation) 可以进行查询3、记录已经被授予访问SQL Server 实例的 Windows用户或者组在操作系统的用户管理中的各项配置，主要是权限方面。4、记录发布与定于使用的快照文件夹及权限设置5、记录全文检索目录与权限设置6、记录 SQL Server 定义的逻辑磁盘备份设备所使用的目录名7、复

15、制所有的数据文件备用8、对所有的作业生成脚本备用当备份了master 数据库的时候，要注意的一点是，目标服务器的数据库文件与源服务器的数据库文件是否相同。如果不一致，那么直接还原的话有可能导致master 数据库不能正常使用。五、数据库还原的几种情况先建立个测试环境：create database dbon primary( name=db_data, filename= c:db_data.mdf),filegroup db_fg1( name = db_fg1_data, filename = c:db_fg1_data.ndf),filegroup db_fg2( name = db_f

16、g2_data, filename = c:db_fg2_data.ndf)log on( name=db_log, filename =c:db.ldf)gocreate table db.dbo.ta(id int) on primarycreate table db.dbo.tb(id int) on db_fg1create table db.dbo.tc(id int) on db_fg21、部分数据库还原backup log db to disk =c:db_log.bak with formatgo名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - -

17、- - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 9 页 - - - - - - - - - - 如果因为某些要求只还原ta 表, 则使用 restore database 语句配合 with partial和 norecovery选项以另一个名称还原数据库和文件组db_fg1, 另外，还将主文件和文件组primary 、事务日志和已还原的文件组中的所有文件移到新位置。日志即得到恢复restore database db_a filegroup=db_fg1 from disk=c:db.bak with norecovery,partial, move

18、 db_data to c:db_data_a.mdf, move db_log to c:db_log_a.ldf, move db_fg1_data to c:db_fg1_data_a.ndf- 恢复日志restore log db_a from disk=c:db_log.bak with recovery2、抢救损坏数据库中的数据在抢救数据库之前，当然还是需要有一个完全备份。要恢复数据文件损坏的数据库中的数据，可以按照下面的步骤来进行： 1）、使用带no_truncate子句的 backup log语句备份损坏数据库中的事务日志。 2）、使用带norecovery子句的 restor

19、e database语句从完全备份中还原数据 3）、使用带norecovery子句的 restore database和 restore log语句从差异备份和日志备份中还原数据 4）、使用带recovery子句的 restore log从第一步中备份的事务日志备份中还原数据示例：- 首先要备份当前日志backup log db to disk=c:db_log.bak with format,no_truncate- 利用文件组备份恢复破坏的文件restore database db from disk=c:db.bak with norecovery- 还原到日志点restore log

20、db from disk=c:db_log.bak with recovery3、将数据还原到指定时间点方法与抢救损坏数据库中的数据相类似，不过就是在最后一步上指定要恢复的时间点。不过因为数据没有损坏，所以备份日志的时候可以不使用no_truncate子句。- 首先 , 备份事务日志 ( 使用事务日志才能还原到指定的时间点)backup log db to disk=c:db_log.bak with formatgo- 接下来 ,我们要先还原完全备份( 还原日志必须在还原完全备份的基础上进行)restore database db from disk=c:db.bak with replac

21、e,norecoverygo- 将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早restore log db from disk=c:db_log.bak with recovery,stopat=时间点go名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 9 页 - - - - - - - - - 4、将数据还原到指定标记点 begin transaction tran1 with markcreate table db.dbo.tb(id in

22、t)commit tran tran1- 事务完成后插入数据insert db.dbo.tb select id from sysobjectsgo- 还原数据到事务标记 tran1 前backup log db to disk=c:db_log.bak with formatdrop database dbrestore database db from disk=c:db.bak with norecoveryrestore log db from disk=c:db_log.bak with stopbeforemark=tran1select count(*) from db.dbo.

23、tb/*-事务标记 tran1 前没有创建表 , 所以会收到错误信息对象名 db.dbo.tb 无效。-*/go- 还原数据库到事务标记 tran1 后drop database dbrestore database db from disk=c:db.bak with norecoveryrestore log db from disk=c:db_log.bak with stopatmark=tran1六、数据库备份与还原中常见的问题1、使用“ backup database pubs to disk=c:pubs.bak时，提示156 错误，”在关键字database 附近有语法错误原因

24、：数据库兼容级别小于70解决方法：把数据库的兼容级别设为802、使用“ backup database pubs to pubs1”提示在sysdevices中没有备份设备pubs1 的条目。原因：在数据库中缺少pubs 备份设备。解决方法：添加pubs1 设备，展开服务器组/ 服务器 / 管理 / 备份 / 新建备份设备3、进行差异备份的时候，提示无法执行数据库%1的差异备份，因为不存在当前数据库备份。请去掉with differential选项后重新发出backup database 以执行数据库的完整备份。原因：在进行差异备份的时候，没有进行完全备份解决方法：先进行完全备份，然后再进行差

25、异备份。4、进行日志备份时，收到“没有当前的数据库备份。此日志备份不能用于前滚先前的数据库备份”原因：进行日志备份的时候，没有进行完全备份或者差异备份5、进行日志备份时，收到错误信息 “当恢复模型为simple 时，不允许使用backup log 语句。 请使用 backup database 或者用 alter database更改恢复模型”原因与解决方法，提示说得很清楚。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 9 页 - - - - - - - - - 6、差

26、异备份和日志备份还原，需要注意： 1）、日志备份还原时的日志备份时间顺序不能颠倒 2）、不能将日志备份和差异备份用作完全备份 3）、进行差异备份还原和日志备份还原时，需要先进行完全备份还原 4）、还原完全备份的时候，如果还要进行还原差异备份或者日志备份的时候，一定要使用norecovery选项。 5）、最后一个还原时，使用recovery选项，使数据库处于运行状态7、数据库还原时，不能还原，提示：“数据库正在使用，所以未能获得对数据库的排他访问权”原因：当对一个数据库进行还原的时候，该数据库必须处于关闭状态（即没有任何的连接）。解决方法：断开所有的连接，修改当前数据库为master （或其他的

27、数据库，一般为master 数据库，因为 master 数据库具有guest 用户，任何用户都可以连接到master ）8、数据库还原操作会自行根据备份文件中的信息和还原选项设置，根据备份文件中的数据创建还原的目标数据库，还原数据库前一般不需要事先创建数据库。七、数据库还原中的孤立用户问题当把数据库备份还原到另一个服务器的时候，可能会产生孤立用户，即数据库中的某个用户没有和sql server中的任何登陆对应起来。解决方法：先在服务器中建立相应的登陆，然后把该登陆与数据库的该用户联系起来。解决方法有下面几种：1、如果该用户没有对象，则重新建立一个登陆，然后把该用户删掉，然后重新建立该用户。在删

28、除该用户之前，一点要先记录该用户的权限2、如果该用户有对象，那么该用户不能直接删除。那么可以使用sp_changeobjectowner hero.tb,dbo（hero 为该孤立用户），然后再使用第一种方法，然后再把该表的所有者再修改回来sp_changeobjectowner tb,hero3、先建立一个登陆，然后使用sp_change_users_login AUTO_FIX,孤立用户名 , 登录名把用户名和登录名联系起来。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 9 页 - - - - - - - - -