185.数据库复制-备份

- 日理万妓 2024-01-20 07:01 3阅读 0赞

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
    
    /*--将一个数据库完整复制成另一个数据库
    
    	借助备份/恢复,将源数据库复制为目标数据库
    
    --邹建 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