SQL remote Recovery

Original: SQL remote recovery

--=============================================--author:dcrenl--Create date:2013-9-5 14:08:35--Description: Web server remote recovery database, remote recovery and remote backup the idea is the same. --but there was a problem releasing the connection at the time of recovery, so a stored procedure to release the connection was added. --it could have been integrated into one but given that other places would also use the release connection so it was taken out alone. --=============================================alter PROCEDURE [dbo]. [Remoterestore] @DataDisk nvarchar (max),--need to map the drive letter @webaddr nvarchar (max),--Web server address @webdisk nvarchar (max) on the database server,- -web Server shared directory (for example: d$ or c$\windows) @Password nvarchar (max),--Web server password @username nvarchar (max),--Web server user name @dataname nvarchar (max),--the database name that needs to be restored @backname nvarchar (max)--the database name that needs to be restored Asbeginset NOCOUNT on; --Open Advanced settings exec sp_configure ' Show advanced options ', 1reconfigure--open xp_cmdshellexec sp_configure ' xp_cmdshell ', 1reconfigure--maps the Web server path to the database server exec (' master.. xp_cmdshell ' net use ' + @DataDisk + ': \ \ ' + @WEBAddr + ' \ ' + @WEBDisk + ' "' + @Password + '"/user: ' + @WEBAddr + ' \ ' + @UserName + ")--the mapping path to start backing up and copying to the Web server declare   @DataBaseName varchar set @DataBaseName = ' Kf_reg_ ' + @ BacknameexEC p_killspid @DataBaseNameexec (' RESTORE DATABASE [' + @DataName + '] from DISK =n ' [email protected]+ ': \ ' [email protected]+ '. Bak ' with REPLACE ')--close the mapping path exec (' Master.. xp_cmdshell ' net use ' + @DataDisk + ':/delete ')--close xp_cmdshellexec sp_configure ' xp_cmdshell ', 0reconfigure--Close Advanced settings exec sp_configure ' show advanced options ',0reconfigureend  ------------------------- ------------------------------------------------------------------------  --Disconnects all users connected to the stored over and ALTER PROC [ DBO]. [P_killspid] (@dbname varchar)   as   begin   declare @sql nvarchar (+)   declare @ spid int   set @sql = ' Declare getspid cursor for   select spid from master.sys.sysprocesses wheredbid= DB_ID (' [email protected]+ ') '   exec (@sql)   open getspid   fetch next from Getspid into @spid   while @ @fetch_status <>-1  begin   exec (' Kill ' [email  Protected]) &NBsp; fetch next from Getspid to @spid   end   close getspid   deallocate Getspid & Nbsp; end    

SQL remote Recovery