四、数据库的迁移
当需要把数据库从一个服务器迁移到另一个服务器的时候,需要从以下几个方面进行准备:
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','孤立用户名',‘登录名’把用户名和登录名联系起来。