数据库自动还原

上文我们介绍了数据库自动备份和上传操作,接下来完成数据库的自动远原功能。

 首先关闭所有用户连接，代码如下；

create proc killDb

@dbname varchar(1000)

as

begin

declare @s varchar(40)

declare tb cursor for

select N'kill '+cast(spid as varchar) from master..sysprocesses

where dbid=db_id(@dbname)

open tb 

fetch next from tb into @s

 while @@fetch_status=0

  exec(@s)

fetch next from tb into @s

close tb

deallocate tb

end

 

 接下来执行还原：

  exec killDb 'Northwind'--关闭所有用户连接
            exec xp_cmdshell '"C:\Program Files\WinRAR\UNRAR.exe" e -df -O+ D:\~temp\Northwind.rar D:\~temp\'
            if day(getdate())=8
            begin
              restore database [Northwind] from disk='D:\~temp\Northwind.bak' with recovery,replace,  MOVE N'mxcement_Data' TO N'e:\Northwind.mdf', 
             MOVE N'mxcement_Log' TO N'e:\Northwind.ldf'--recovery 无法还原其它日志文件
            end
            else
            begin
              restore database [Northwind] from disk='D:\~temp\Northwind.bak' with norecovery,replace  MOVE N'mxcement_Data' TO N'e:\Northwind.mdf', 
              MOVE N'mxcement_Log' TO N'e:\Northwind.ldf'--norecovery 可以还原其它日志文件
              restore database [Northwind] from disk='D:\~temp\Northwind.cha' with recovery
            end