use master
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_CopyDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_CopyDb]
GO

/*--將一個數(shù)據(jù)庫完整復制成另一個數(shù)據(jù)庫

	借助備份/恢復,將源數(shù)據(jù)庫復制為目標數(shù)據(jù)庫

--鄒建 2003.10(引用請保留此信息)--*/

/*--調用示例

	exec p_CopyDb @sdbname=N'pubs'
--*/
create proc p_CopyDb
@sdbname sysname=N'',   --定義要復制的數(shù)據(jù)庫名,默認為當前數(shù)據(jù)庫
@ddbname sysname=N'',   --定義復制后生成的數(shù)據(jù)庫名,默認為當前數(shù)據(jù)庫名+_bak
@overexist bit=1,       --是否覆蓋已經(jīng)存在的數(shù)據(jù)庫
@killuser bit=1         --是否關閉用戶使用進程,僅@overexist=1時有效
as
declare @s Nvarchar(4000),@bkfile Nvarchar(1000),@move Nvarchar(4000)

--參數(shù)檢查
if @overexist is null set @overexist=0
if @killuser is null set @killuser=0

--檢查復制的源庫名
if isnull(@sdbname,N'')=N''
	set @sdbname=db_name()
else if db_id(@sdbname) is null
begin
	raiserror(N'數(shù)據(jù)庫"%s"無效',1,16,@sdbname)
	return
end

--檢查目標數(shù)據(jù)庫
if isnull(@ddbname,N'')='' 
	set @ddbname=@sdbname+N'_bak'
if @overexist=0 and db_id(@ddbname) is not null
begin
	raiserror(N'目標數(shù)據(jù)庫"%s"已經(jīng)存在',1,16,@ddbname)
	return
end

--臨時備份文件名
select top 1 @bkfile=rtrim(reverse(filename)) 
from master.dbo.sysfiles 
where name=N'master'
select @bkfile=stuff(@bkfile,1,charindex('\',@bkfile),N'')
	,@bkfile=reverse(stuff(@bkfile,1,charindex('\',@bkfile),N''))
		+N'\BACKUP\'+cast(newid() as nvarchar(36))+N'.bak'

--數(shù)據(jù)文件移動語句
set @s=N'set @move=N''''
select @move=@move
	+N'',move ''+quotename(rtrim(name),N'''''''')
	+N'' to ''+quotename(rtrim(case 
		when charindex(N'
	+quotename(@sdbname,N'''')
	+N',filename)>0
		then stuff(filename,charindex(N'
	+quotename(@sdbname,N'''')
	+N',filename),'
	+cast(len(@sdbname) as nvarchar)
	+N',N'+quotename(@ddbname,N'''')+N')
		else reverse(stuff(
			reverse(filename),
			charindex(''\'',reverse(filename)),
			0,
			+N''_''+reverse(N'+quotename(@ddbname,N'''')+N')))
		end),N'''''''')
from '+quotename(@sdbname)+N'.dbo.sysfiles'
exec sp_executesql @s,N'@move Nvarchar(4000) out',@move out

--備份源數(shù)據(jù)庫
set @s=N'backup database '+quotename(@sdbname)+N' to disk=@bkfile with format'
exec sp_executesql @s,N'@bkfile Nvarchar(1000)',@bkfile

--如果需要,做關閉用戶進程處理
if @killuser=1 and db_id(@ddbname) is not null
begin
	declare tb cursor local
	for 
	select N'kill '+cast(spid as varchar)
	from master.dbo.sysprocesses
	where dbid=db_id(@ddbname)
	open tb
	fetch tb into @s
	while @@fetch_status=0
	begin  
		exec sp_executesql @s
		fetch tb into @s
	end  
	close tb
	deallocate tb
end

--還原為目標數(shù)據(jù)庫
set @s=N'restore database '
	+quotename(@ddbname)
	+N' from disk=@bkfile with replace'
	+@move
exec sp_executesql @s,N'@bkfile Nvarchar(1000)',@bkfile

--刪除臨時備份文件
set @s='del "'+@bkfile+'"'
exec master..xp_cmdshell @s,no_output
go