SQL代码实现数据库附加、分离、备份与还原备份

use master
/*******************************************************
备份数据库
*******************************************************/
if exists(select 1 from sys.procedures where name='sp_BackupDB')
drop procedure sp_BackupDB
go
create procedure sp_BackupDB
@savePath nvarchar(4000) -- 备份数据库保存位置(目录) 
,@dbName nvarchar(4000) -- 需要进行备份的数据库
,@bakName nvarchar(4000) -- 备份文件的名称(不含扩展名)
as begin
declare @sql nvarchar(4000)
/* 验证路径 */
if(charindex('/',reverse(@savePath))!=1) begin
set @savePath=@savePath+'/'
end
/* 拼SQL并执行 */
set @sql='backup database '+@dbName+' to disk='''+@savePath+@bakName+'.bak'''
exec sp_executesql @sql

/* 返回执行结果(1=成功,0=失败) */
if(@@error=0) begin
return 1
end
return 0
end

/*******************************************************
还原数据库
*******************************************************/
if exists(select 1 from sys.procedures where name='sp_RestoreDB')
drop procedure sp_RestoreDB
go
create procedure sp_RestoreDB
/* 数据库还原后的保存位置(目录)(使用系统默认保存位置:-1) */
@savePath nvarchar(4000)
,@backFile nvarchar(4000) -- 需要还原的数据库备份文件
,@defaultName nvarchar(4000) -- 数据库原始名称(备份的原数据库名称)不包含扩展名
/* 为数据库重命名(使用数据库默认名称:-1)不包含扩展名
如果目录已存在该名称的数据库,将会被覆盖 */
,@dbName nvarchar(4000)
as begin

declare @newName nvarchar(4000),@sql nvarchar(4000)

/*分离数据库*/
set @sql = 'sp_detach_db '+@defaultName
exec(@sql)

/* 获取数据库名称 */
if(@dbName='-1') begin
set @newName=@defaultName
end else begin
set @newName=@dbName
end
/* 结束所有对当前数据库的连接 */
if exists(select 1 from sys.sysprocesses where dbid=db_id(@defaultName)) begin
declare #cs_spid cursor -- 声明游标
for
select #cs_spid=convert(varchar,spid) from sys.sysprocesses where dbid=db_id(@defaultName)
open #cs_spid
declare @spid varchar(20)
fetch next from #cs_spid into @spid -- 赋值并前进到下一条
while(@@fetch_status=0) begin -- 在fetch失败前执行
exec ('kill '+@spid) -- 结束对操作库的连接(exec执行SQL语句1)
fetch next from #cs_spid into @spid
end
close #cs_spid
deallocate #cs_spid -- 释放游标
end
/* 创建执行语句 */
set @sql='restore database '+@newName+' from disk='''+@backFile+''' with replace'
if(@savePath!='-1') begin
-- 验证路径
if(charindex('/',reverse(@savePath))!=1) begin
set @savePath=@savePath+'/'
end
set @sql=@sql+', move '''+@defaultName+'_data'' to '''+@savePath+@newName+'_data.mdf'''
set @sql=@sql+', move '''+@defaultName+'_log'' to '''+@savePath+@newName+'_log.ldf'''
end
/* 执行操作 */
exec sp_executesql @sql -- (exec执行SQL语句2)
/* 返回执行结果(1=成功,0=失败) */
if(@@error=0) begin
return 1
end
return 0
end

/*备份*/
use master
declare @savePath1 nvarchar(4000),@dbName1 nvarchar(4000),@bakName1 nvarchar(4000)
set @savePath1='d:\backup/'
set @dbName1='ParkAdv'
set @bakName1='ParkAdv'+CONVERT(varchar(100), GETDATE(), 112)
exec sp_BackupDB @savePath1,@dbName1,@bakName1

/*还原*/
use master
declare @savePath1 varchar(max),@backFile1 varchar(max),@defaultName1 varchar(max),@dbName1 varchar(max)
set @savePath1='d:\database/'
set @backFile1='d:\backup/ParkAdv.bak'
set @defaultName1= 'ParkAdv'
set @dbName1='-1'
exec sp_RestoreDB @savePath1,@backFile1,@defaultName1,@dbName1

/*附加*/
use master
declare @dbname varchar(max),@filename1 varchar(max),@filename2 varchar(max)
set @dbname='ParkAdv'
set @filename1='d:\database\ParkAdv_data.mdf'
set @filename2='d:\database\ParkAdv_log.ldf'
EXEC sp_attach_db @dbname,@filename1,@filename2

/*分离*/
use master
sp_detach_db ParkAdv