AI编程社区

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

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

   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