MSSQL备份与恢复数据存取过程

if exists(
select * from sysobjects
where name='BackupDB' and xtype='p'
)
begin
drop proc BackupDB
end
go
create procedure BackupDB
@dataname nvarchar(1024),
@filename nvarchar(1024)
as
 declare  @sql nvarchar(1024)
 set @sql='backup database '+@dataname+' to disk=N'''+@filename+''' with Format'
 exec sp_executesql @sql
go
if exists(
select * from sysobjects
where name='RestoreDB' and xtype='p'
)
begin
drop proc RestoreDB
end
go
create procedure RestoreDB
@dataname nvarchar(1024),
@filename nvarchar(1024)
as
  declare  @masterpath nvarchar(1024)
  declare  @mdfpath nvarchar(1024)
  declare  @logpath nvarchar(1024)
  declare  @logname nvarchar(1024)
  declare  @sql nvarchar(1024)
  select  @masterpath=filename from sysaltfiles where name='master'
  set @mdfpath=rtrim(replace(@masterpath,'master.mdf',@dataname+'.mdf'))
  set @logpath=rtrim(replace(@masterpath,'master.mdf',@dataname+'_log.ldf'))
  set @logname=@dataname+'_log'

  set @sql=N'alter database '+@dataname+' set offline WITH ROLLBACK IMMEDIATE;'
  exec sp_executesql @sql
  Set @Sql=N'restore database '+@dataname+' from disk=N'''+@filename+''' '
  Set @Sql=@Sql+'with '
  Set @Sql=@Sql+'move N'''+@dataname+''' TO N'''+@mdfpath+''','
  Set @Sql=@Sql+'move N'''+@logname+''' To N'''+@logpath+''',replace'	  
  exec sp_executesql @Sql
  set @sql=N'alter database '+@dataname+' set online WITH ROLLBACK IMMEDIATE ;'
  exec sp_executesql @sql

转载于:https://www.cnblogs.com/fyen/archive/2011/03/19/1988754.html