197.数据库文件移动

深藏阁楼爱情的钟 2024-01-20 07:07 34阅读 0赞

use master	--必须在master数据库中创建
    go
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_MoveDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[p_MoveDb]
    GO
    
    /*--将一个数据库的数据文件移动到指定的目录
    
    --(引用请保留此信息)--*/
    
    /*--调用示例
    
    	exec p_MoveDb 'testa','c:\'
    --*/
    create proc p_MoveDb
    @sdbname sysname,	--定义要移动的数据库名
    @newpath sysname	--存放数据文件的新目录名
    as
    declare @sql varchar(8000),@bpath varchar(8000),@rpath varchar(8000),@s nvarchar(4000)
    
    --参数检测
    set @sql=''
    if isnull(@sdbname,'')='' set @sql='必须指定要处理的数据库名'
    if db_id(@sdbname) is null set @sql=@sql+char(13)+'指定的数据库不存在!'
    if isnull(@newpath,'')='' set @sql=@sql+char(13)+'必须指定新的数据库目录'
    if @sql<>''
    begin
    	print @sql
    	return
    end
    
    --生成数据库备份语句,进行数据库备份
    select @bpath=@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 ['+@sdbname
    	+'] from disk='''+@bpath+''''+' with file=1,replace'
    
    if right(@newpath,1)='\' set @newpath=left(@newpath,len(@newpath)-1)
    
    set @s='select @sql=@sql+'',move ''''''+rtrim(name)+'''''' to ''''''+@newpath+rtrim(right(filename,charindex(''\'',reverse(filename))))+'''''''' from ['+@sdbname+']..sysfiles'
    exec sp_executesql @s
    	,N'@sql varchar(8000) out,@newpath sysname'
    	,@sql out,@newpath
    
    --关闭用户进程
    declare hCForEach cursor global for
    select s='kill '+cast(spid as varchar) 
    from master..sysprocesses where dbid=db_id(@sdbname)
    exec sp_msforeach_worker '?'
    
    --恢复数据库
    exec(@sql)
    go