sql脚本：将一个数据库完整复制到另一个数据库（转）
/*–将一个数据库完整复制成另一个数据库
—邹建 2003.10–*/
/*–调用示例
exec p_CopyDb @ddbname=\’test\’
–*/
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
create proc p_CopyDb
@sdbname sysname=\’\’, —定义要复制的数据库名,默认为当前数据库
@ddbname sysname, —定义复制后生成的数据库名
@overexist bit=1, —是否覆盖已经存在的数据库
@killuser bit=1 —是否关闭用户使用进程,仅@overexist=1时有效
as
declare @sql varchar(8000),@bpath varchar(8000),@rpath varchar(8000)
—得到要复制的数据库名
if isnull(@sdbname,\’\’)=\’\’ set @sdbname=db_name()
—得到临时备份数据目录及文件名
select @bpath=rtrim(reverse(filename)) from master..sysfiles where name=\’master\’
select @bpath=substring(@bpath,charindex(\'”\’,@bpath)+1,8000)
,@bpath=reverse(substring(@bpath,charindex(\'”\’,@bpath),8000))+\’BACKUP”\’
+@sdbname+\’_\’+convert(varchar,getdate(),112)
+\’_\’+replace(convert(varchar,getdate(),108),\’:\’,\’\’)
+\’.bak\’
—生成数据库备份语句,进行数据库备份
set @sql=\’backup database \’+@sdbname
+\’ to disk=\’\’\’+@bpath
+\’\’\’ with NOINIT\’
exec(@sql)
—根据备份文件恢复成新的数据库(完成复制工作)
set @sql=\’restore database \’+@ddbname
+\’ from disk=\’\’\’+@bpath+\’\’\’\’
+\’ with file=1\’
+case when @overexist=1 then \’,replace\’ else \’\’ end
—得到数据库存放的默认目录
—得到SQL安装时设置的数据文件路径
select @rpath=rtrim(reverse(filename)) from master..sysfiles where name=\’master\’
select @rpath=reverse(substring(@rpath,charindex(\'”\’,@rpath),8000))
—添加移动逻辑文件的处理
—从备份文件中获取逻辑文件名
declare @lfn nvarchar(128),@tp char(1),@i int
—创建临时表,保存获取的信息
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
—从备份文件中获取信息
insert into #tb exec(\’restore filelistonly from disk=\’\’\’+@bpath+\’\’\’\’)
declare #f cursor for select ln,tp from #tb
open #f
fetch next from #f into @lfn,@tp
set @i=0
while @@fetch_status=0
begin
select @sql=@sql+\’,move \’\’\’+@lfn+\’\’\’ to \’\’\’+@rpath+@ddbname+cast(@i as varchar)
+case @tp when \’D\’ then \’.mdf\’\’\’ else \’.ldf\’\’\’ end
,@i=@i+1
fetch next from #f into @lfn,@tp
end
close #f
deallocate #f
—关闭用户进程处理
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(@ddbname)
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)
—删除备份的临时文件
set @sql=\’del “\’+@bpath+\'”\’
exec master..xp_cmdshell @sql,no_output
select @sql,@bpath,@rpath
go
文章来源：http://topic.csdn.net/t/20031217/17/2574348.html