CREATE proc RestoreDb @bkfile nvarchar(1000),@dbname sysname='',@dbpath nvarchar(260)='',@retype nvarchar(10)='DB',@filenumber int=1, @overexist bit=1,@killuser bit=1 as declare @sql varchar(8000) if isnull(@dbname,'')=''select @sql=reverse(@bkfile),@sql=case when charindex('.',@sql)=0 then @sql else substring(@sql,charindex('.',@sql)+1,1000) end ,@sql=case when charindex('\',@sql)=0 then @sql else left(@sql,charindex('\',@sql)-1) end,@dbname=reverse(@sql) set @sql='restore '+case @retype when 'LOG' then 'log ' else 'database ' end+@dbname+' from disk='''+@bkfile+''''+' with file='+cast(@filenumber as varchar) +case when @overexist=1 and @retype in('DB','DBNOR') then ',replace' else '' end +case @retype when 'DBNOR' then ',NORECOVERY' else ',RECOVERY' end print @sql if @overexist=1 and @killuser=1 begin declare @spid varchar(20) declare #spid cursor for select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname) open #spid fetch next from #spid into @spid while @@fetch_status=0 begin exec('kill '+@spid) fetch next from #spid into @spid End close #spid deallocate #spid End exec (@sql) using(SqlConnection Sql=new SqlConnection("")) { SqlCommand cmd=new SqlCommand("RestoreDb",Sql); string Str="exec RestoreDb @bkfile='"+strPath+"',@dbname='test'"; Sql.Open(); Sql.ChangeDatabase("master"); cmd.Connection = Sql; cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 600; cmd.CommandText = Str; try { cmd.ExecuteNonQuery(); Sql.ChangeDatabase(""); cmd.Dispose(); Sql.Close(); } catch(Exception ex) { Sql.ChangeDatabase(""); cmd.Dispose(); Sql.Close(); return; } }
as declare @sql varchar(8000) if isnull(@dbname,'')=''select @sql=reverse(@bkfile),@sql=case when charindex('.',@sql)=0 then @sql else substring(@sql,charindex('.',@sql)+1,1000) end ,@sql=case when charindex('\',@sql)=0 then @sql else left(@sql,charindex('\',@sql)-1) end,@dbname=reverse(@sql)
set @sql='restore '+case @retype when 'LOG' then 'log ' else 'database ' end+@dbname+' from disk='''+@bkfile+''''+' with file='+cast(@filenumber as varchar) +case when @overexist=1 and @retype in('DB','DBNOR') then ',replace' else '' end +case @retype when 'DBNOR' then ',NORECOVERY' else ',RECOVERY' end
print @sql
if @overexist=1 and @killuser=1
begin declare @spid varchar(20) declare #spid cursor for select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname) open #spid fetch next from #spid into @spid while @@fetch_status=0 begin exec('kill '+@spid) fetch next from #spid into @spid End close #spid deallocate #spid End
exec (@sql)
using(SqlConnection Sql=new SqlConnection(""))
{
SqlCommand cmd=new SqlCommand("RestoreDb",Sql);
string Str="exec RestoreDb @bkfile='"+strPath+"',@dbname='test'";
Sql.Open();
Sql.ChangeDatabase("master");
cmd.Connection = Sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 600;
cmd.CommandText = Str;
try
{
cmd.ExecuteNonQuery();
Sql.ChangeDatabase("");
cmd.Dispose();
Sql.Close();
}
catch(Exception ex)
{
Sql.ChangeDatabase("");
cmd.Dispose();
Sql.Close();
return;
}
}