数据库备份与恢复（二）

四、数据库的迁移

当需要把数据库从一个服务器迁移到另一个服务器的时候，需要从以下几个方面进行准备：

1、备份系统数据库，master、mode、msdb。对master数据库进行备份是因为master数据库中系统的登陆信息，如果不对master进行备份，那么在新的服务器上不对master数据库进行恢复的话，有可能造成孤立用户。

2、记录原服务器上SQL Server的排序规则。

通过 select serverproperty(‘collation’)可以进行查询

3、记录已经被授予访问SQL Server实例的Windows用户或者组在操作系统的用户管理中的各项配置，主要是权限方面。

4、记录发布与定于使用的快照文件夹及权限设置

5、记录全文检索目录与权限设置

6、记录SQL Server定义的逻辑磁盘备份设备所使用的目录名

7、复制所有的数据文件备用

8、对所有的作业生成脚本备用

当备份了master数据库的时候，要注意的一点是，目标服务器的数据库文件与源服务器的数据库文件是否相同。如果不一致，那么直接还原的话有可能导致master数据库不能正常使用。

五、数据库还原的几种情况

先建立个测试环境：

create database db

on 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(

     name='db_log',

     filename ='c:/db.ldf')

go

create table db.dbo.ta(id int) on [primary]

create table db.dbo.tb(id int) on db_fg1

create table db.dbo.tc(id int) on db_fg2

1、部分数据库还原

   backup log db to disk ='c:/db_log.bak' with format

go

--如果因为某些要求只还原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 '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 recovery

2、抢救损坏数据库中的数据

   在抢救数据库之前，当然还是需要有一个完全备份。要恢复数据文件损坏的数据库中的数据，可以按照下面的步骤来进行：

   1）、使用带no_truncate子句的backup log语句备份损坏数据库中的事务日志。

   2）、使用带norecovery子句的restore 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 db from disk='c:/db_log.bak' with recovery

3、将数据还原到指定时间点

   方法与抢救损坏数据库中的数据相类似，不过就是在最后一步上指定要恢复的时间点。不过因为数据没有损坏，所以备份日志的时候可以不使用no_truncate子句。

--首先,备份事务日志(使用事务日志才能还原到指定的时间点)

backup log db to disk='c:/db_log.bak' with format

go

--接下来,我们要先还原完全备份(还原日志必须在还原完全备份的基础上进行)

restore database db from disk='c:/db.bak' with replace,norecovery

go

--将事务日志还原到删除操作前（这里的时间对应上面的删除时间，并比删除时间略早

restore log db from disk='c:/db_log.bak' with recovery,stopat=’时间点’

go

4、将数据还原到指定标记点

 begin transaction tran1 with mark

create table db.dbo.tb(id int)

commit tran tran1

--事务完成后插入数据

insert db.dbo.tb select id from sysobjects

go

--还原数据到事务标记 tran1 前

backup log db to disk='c:/db_log.bak' with format

drop database db

restore database db from disk='c:/db.bak' with norecovery

restore log db from disk='c:/db_log.bak' with stopbeforemark='tran1'

select count(*) from db.dbo.tb

/*--事务标记 tran1 前没有创建表,所以会收到错误信息

对象名 'db.dbo.tb' 无效。

--*/

go

--还原数据库到事务标记 tran1 后

drop database db

restore database db from disk='c:/db.bak' with norecovery

restore log db from disk='c:/db_log.bak' with stopatmark='tran1'

六、数据库备份与还原中常见的问题

1、使用“backup database pubs to disk=‘c:/pubs.bak’时，提示156错误，”在关键字database附近有语法错误

   原因：数据库兼容级别小于70

   解决方法：把数据库的兼容级别设为80

2、使用“backup database pubs to pubs1”提示在sysdevices中没有备份设备pubs1的条目。

   原因：在数据库中缺少pubs备份设备。

   解决方法：添加pubs1设备，展开服务器组/服务器/管理/备份/新建备份设备

3、进行差异备份的时候，提示无法执行数据库%1的差异备份，因为不存在当前数据库备份。请去掉with differential选项后重新发出backup database 以执行数据库的完整备份。

   原因：在进行差异备份的时候，没有进行完全备份

   解决方法：先进行完全备份，然后再进行差异备份。

4、进行日志备份时，收到“没有当前的数据库备份。此日志备份不能用于前滚先前的数据库备份”

   原因：进行日志备份的时候，没有进行完全备份或者差异备份

5、进行日志备份时，收到错误信息“当恢复模型为simple时，不允许使用backup log语句。请使用backup database或者用alter database更改恢复模型”

   原因与解决方法，提示说得很清楚。

6、差异备份和日志备份还原，需要注意：

   1）、日志备份还原时的日志备份时间顺序不能颠倒

   2）、不能将日志备份和差异备份用作完全备份

   3）、进行差异备份还原和日志备份还原时，需要先进行完全备份还原

   4）、还原完全备份的时候，如果还要进行还原差异备份或者日志备份的时候，一定要使用norecovery选项。

   5）、最后一个还原时，使用recovery选项，使数据库处于运行状态

7、数据库还原时，不能还原，提示：“数据库正在使用，所以未能获得对数据库的排他访问权”

   原因：当对一个数据库进行还原的时候，该数据库必须处于关闭状态（即没有任何的连接）。

   解决方法：断开所有的连接，修改当前数据库为master（或其他的数据库，一般为master数据库，因为master数据库具有guest用户，任何用户都可以连接到master）

8、数据库还原操作会自行根据备份文件中的信息和还原选项设置，根据备份文件中的数据创建还原的目标数据库，还原数据库前一般不需要事先创建数据库。

七、数据库还原中的孤立用户问题

当把数据库备份还原到另一个服务器的时候，可能会产生孤立用户，即数据库中的某个用户没有和sql server中的任何登陆对应起来。

解决方法：

先在服务器中建立相应的登陆，然后把该登陆与数据库的该用户联系起来。解决方法有下面几种：

1、如果该用户没有对象，则重新建立一个登陆，然后把该用户删掉，然后重新建立该用户。在删除该用户之前，一点要先记录该用户的权限

2、如果该用户有对象，那么该用户不能直接删除。那么可以使用sp_changeobjectowner 'hero.tb','dbo'（hero为该孤立用户），然后再使用第一种方法，然后再把该表的所有者再修改回来sp_changeobjectowner 'tb','hero'

3、先建立一个登陆，然后使用sp_change_users_login  'AUTO_FIX','孤立用户名',‘登录名’把用户名和登录名联系起来。