use master	--必須在master數(shù)據(jù)庫中創(chuàng)建
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

/*--將一個(gè)數(shù)據(jù)庫的數(shù)據(jù)文件移動到指定的目錄

--鄒建 2004.08(引用請保留此信息)--*/

/*--調(diào)用示例

	exec p_MoveDb 'testa','c:\'
--*/
create proc p_MoveDb
@sdbname sysname,	--定義要移動的數(shù)據(jù)庫名
@newpath sysname	--存放數(shù)據(jù)文件的新目錄名
as
declare @sql varchar(8000),@bpath varchar(8000),@rpath varchar(8000),@s nvarchar(4000)

--參數(shù)檢測
set @sql=''
if isnull(@sdbname,'')='' set @sql='必須指定要處理的數(shù)據(jù)庫名'
if db_id(@sdbname) is null set @sql=@sql+char(13)+'指定的數(shù)據(jù)庫不存在!'
if isnull(@newpath,'')='' set @sql=@sql+char(13)+'必須指定新的數(shù)據(jù)庫目錄'
if @sql<>''
begin
	print @sql
	return
end

--生成數(shù)據(jù)庫備份語句,進(jìn)行數(shù)據(jù)庫備份
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)

--根據(jù)備份文件恢復(fù)成新的數(shù)據(jù)庫(完成數(shù)據(jù)文件移動工作)

--生成還原數(shù)據(jù)庫時(shí)的文件移動語句
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

--關(guān)閉用戶進(jìn)程
declare hCForEach cursor global for
select s='kill '+cast(spid as varchar) 
from master..sysprocesses where dbid=db_id(@sdbname)
exec sp_msforeach_worker '?'

--恢復(fù)數(shù)據(jù)庫
exec(@sql)
go