SQL SERVER 2005、2008终极还原办法

数据库 专栏收录该内容
3 篇文章 0 订阅

号外:

一直在做公司的crm系统维护,今天早上做改动的时候,不小心一个存储过程就把数据库整库truncate了。还好数据一直都有备份,而且SQL server的图形界面操作强大,很简单就能还原,唯一的意外就是我从来没试过还原系统。
就是以下这段代码祸害的,方法是建立一个临时表mytemp,临时表中两列,分别是sql语句和行号。SQL语句的内容都是truncate table +表名,总共4百多个表,执行exec命令全都清空了。
killer自白:
declare @str varchar(200),@countrow int
select 'truncate table '+name+'' as NAME,ROW_NUMBER() over(order by name ) as ID into #mytemp from sysobjects where type = 'U'
	select @countrow = COUNT(1) from sysobjects where type = 'U'
	while @countrow>0
	begin
	select @str = NAME from #mytemp where  ID = @countrow
	exec(@str)
	select @countrow = @countrow - 1	
	end

备份:

系统在停用40分钟后恢复正常,得益于平时有备份,不然我就呵呵了。平时的备份方法如下,因为是真实数据库,姑且化名为xcjdb。

1、首先建立一个作业

2、作业设定每天凌晨3点执行,执行步骤有两个,都是通过TSQL备份

2.1备份到本机d盘

backup database xcjdbto disk='d:\backup\xcjdb.bak'

2.2备份到异地

 调用xp_cmdshell生成window命令shell,登陆到另一台机,然后执行backup备份命令。

exec master..xp_cmdshell 'net use \\10.200.100.56\xcjdbbackup crmtest@2013 /user:10.200.100.56\Adminstrator'
backup database pdn to disk='\\10.200.100.56\pdnbackup\pdn.bak' with init
系统的数据每天一备,每次备份删除覆盖旧的备份, 没办法空间不足。

xp_cmdshell命令

简介: SQL SERVER xp_cmdshell命令相当于你使用window cmd,可以执行window相关指令。xp_cmdshell位于master库

格式:exec master..xp_cmdshell content-string,exec 和master前缀可省,content-string为varchar(4000)或varchar(8000),内容是你要的window命令。

如 xp_cmdshell 'whoami'

 xp_cmdshell默认不适用,通过这个命令可以做一些越位的事情,比如在你的电脑上创建文件,或者删除文件Σ( ° △ °|||)︴,另外就是备份了一定要测试是否能正确还原,能备份不等于能还原,今天早上还原数据库的时候碰到很多麻烦,实在紧张的小心脏受不了,还好搞定了。

还原:

  简单正常的还原步骤,直接贴图。

1、在资源管理器数据库右键选择还原

2、选择要还原的数据库,还原的源我选择源设备,然后找到xcjdb.bak,勾选后确定就开始还原。

或者是在查询窗口中使用restore命令还原


  理论上就是以上两步,但还原之路不会这么一帆风顺滴~~

  两个难题

1、无法处理数据库 'XXXXX',因为它正由此会话使用。建议在执行此操作时使用 master 数据库。

第一个问题就是这个,我试过用查询窗口restore命令登陆碰到这个问题,第一个反应就是输入  use master,网上很多人也是给这个解决办法,但是不行。最好的方式是不要使用查询窗口,使用资源管理器右键还原,登陆资源管理器时不要使用SqlServer验证方式,改为window验证方式。

2、因为数据库正在使用,所以无法获得对数据库的独占访问权

要还原数据库,就必须先保证外面没有在连接使用数据库。如果有人正持有连接,还原就会提示该错误。所以要把连接到SQL server的应用先停了,防止外面不断地连接(在我这里就是crm,因为只有一个应用,我把JVM关了,如果有多个系统就要卸载单个应用)。另外需要写sql 代码,把所有连接到该数据库的连接kill掉,前面那一步是为了防止kill掉进程又不断有人连接,sql如下

use master

declare @dbname varchar ( 20)

set @dbname = 'v091222' --要kill掉连接的dbname


declare @sql nvarchar ( 500)

declare @spid int --SPID 值是当用户进行连接时指派给该连接的一个唯一的整数

set @sql = 'declare getspid cursor for

select spid from sysprocesses where dbid=db_id(''' + @dbname + ''')'                                                                                       --重要语句,从sysprocesses获取所有连接着指定库的进程号,sysprocesses是master的对象,需要先use master才能查看该对象

exec ( @sql )

open getspid

fetch next from getspid into @spid

while @@fetch_status <>- 1-- 如果 FETCH 语句没有执行失败或此行不在结果集中。

begin

exec ( 'kill ' + @spid ) -- 终止正常连接

fetch next from getspid into @spid

end

close getspid

deallocate getspid
以上参考引用了andy_tigger的文章 因为数据库正在使用,所以无法获得对数据库的独占访问权,他的解释更全面.网上的绝大部分解决办法最多也就到这里,但是我还是提示这个错误,我使用tsql查询,确实没有外界连接数据库(或者只是我没查到?)。偶然情况下查到还有一个办法,将数据库模式设为单个用户,

1、选择数据库右键属性


2、选择“选项”,设置“限制访问”为single user,还原后记得要回来设回去。

在设置完后我在进行还原,奇迹出现,当当当成功了。很艰难的过程,之前我测试过还原数据库,但是一直没成功,就算了。今天是正式数据库问题,逼一下,果然效率高很多。相信这里的方法应用对每一个要还原数据库遇到困难的同学都有帮助。

 

附:1、不同版本数据库备份数据,不能用来还原。

        2、还原数据库时,尽量使用window身份,实在不行再用SqlServer身份,减少意外。

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

木木守护神

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值