use master goif 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/*--将一个数据库完整复制成另一个数据库 借助备份/恢复,将源数据库复制为目标数据库--邹建 2003.10(引用请保留此信息)--*//*--调用示例 exec p_CopyDb @sdbname=N'pubs' --*/ create proc p_CopyDb @sdbname sysname=N'', --定义要复制的数据库名,默认为当前数据库 @ddbname sysname=N'', --定义复制后生成的数据库名,默认为当前数据库名+_bak @overexist bit=1, --是否覆盖已经存在的数据库 @killuser bit=1 --是否关闭用户使用进程,仅@overexist=1时有效 as declare @s Nvarchar(4000),@bkfile Nvarchar(1000),@move Nvarchar(4000)--参数检查 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'数据库"%s"无效',1,16,@sdbname) return end--检查目标数据库 if isnull(@ddbname,N'')='' set @ddbname=@sdbname+N'_bak' if @overexist=0 and db_id(@ddbname) is not null begin raiserror(N'目标数据库"%s"已经存在',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'--数据文件移动语句 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--备份源数据库 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--还原为目标数据库 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
backup database 数据库名to disk='备份到的路径'
goif 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/*--将一个数据库完整复制成另一个数据库 借助备份/恢复,将源数据库复制为目标数据库--邹建 2003.10(引用请保留此信息)--*//*--调用示例 exec p_CopyDb @sdbname=N'pubs'
--*/
create proc p_CopyDb
@sdbname sysname=N'', --定义要复制的数据库名,默认为当前数据库
@ddbname sysname=N'', --定义复制后生成的数据库名,默认为当前数据库名+_bak
@overexist bit=1, --是否覆盖已经存在的数据库
@killuser bit=1 --是否关闭用户使用进程,仅@overexist=1时有效
as
declare @s Nvarchar(4000),@bkfile Nvarchar(1000),@move Nvarchar(4000)--参数检查
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'数据库"%s"无效',1,16,@sdbname)
return
end--检查目标数据库
if isnull(@ddbname,N'')=''
set @ddbname=@sdbname+N'_bak'
if @overexist=0 and db_id(@ddbname) is not null
begin
raiserror(N'目标数据库"%s"已经存在',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'--数据文件移动语句
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--备份源数据库
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--还原为目标数据库
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