197.数据库文件移动

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