看csdn上看到一朋友写了一个《 贴个SQL小工具--移动数据库物理文件》
http://topic.csdn.net/u/20080712/16/5c738254-f93e-4623-a666-e7f753dbdb58.html?seed=1415079037
使用是方法是先设置数据库脱机,再移动数据库文件,然后修改数据库的存储文件路径,最后再设置数据库联机。
这里我写的使用的是,先分离数据库,再移动数据库文件,然后再附加新数据库文件的方法:
Use
master
Go
If Object_id ( ' sp_MoveDB ' , ' P ' ) Is Not null
Drop Proc sp_MoveDB
Go
Create Proc sp_MoveDB
(
@DataBase sysname,
@PathTo nvarchar ( 1024 )
)
As
Declare
@Sql nvarchar ( max ),
@AttachDatabase nvarchar ( max ),
@Error nvarchar ( 1024 ),
@Dir varchar ( 1024 ),
@ok int
If db_id ( @DataBase ) Is null
Begin
Set @Error = ' 错误的数据库名: ' + @DataBase
Raiserror 50001 @Error
Return
End
Set @Dir = ' Dir ' + @PathTo
Exec @ok = xp_cmdshell @Dir ,No_output
If @ok <> 0
Begin
Set @Error = ' 错误的文件路径: ' + @PathTo
Raiserror 50001 @Error
Return
End
Select @DataBase = name From sys.databases Where name = @DataBase
Print ' 数据库: ' + @DataBase + Char ( 13 ) + Char ( 10 ) + ' 正在移动数据库文件,请稍候 '
Select @Sql = Isnull ( @Sql + Char ( 13 ) + Char ( 10 ), '' ) + ' Kill ' + Rtrim (spid) From sys.sysprocesses Where db_name (dbid) = @DataBase
Exec ( @Sql )
Set @Sql = null
Select
@Sql = Isnull ( @Sql + Char ( 13 ) + Char ( 10 ), '' ) + ' Exec xp_cmdshell '' Move ' + physical_name + Char ( 32 ) + @PathTo +Right (physical_name, Charindex ( ' \ ' , Reverse (physical_name))) + ''' ,No_output ' ,
@AttachDatabase = Isnull ( @AttachDatabase + ' , ' , ' Create Database ' + Quotename ( @DataBase ) + ' On ' ) + ' (Filename= ''' + @PathTo +Right (physical_name, Charindex ( ' \ ' , Reverse (physical_name))) + ''' ) '
From sys.master_files Where database_id = db_id ( @DataBase )
Set @Sql = ' Exec sp_detach_db ' + Quotename ( @DataBase ) + Char ( 13 ) + Char ( 10 ) + @Sql + Char ( 13 ) + Char ( 10 ) + @AttachDatabase + ' For Attach '
Exec ( @Sql )
If @@Error = 0
Print ' 完成移动数据库文件. '
Else
Print ' 移动数据库文件失败. '
Go
If Object_id ( ' sp_MoveDB ' , ' P ' ) Is Not null
Drop Proc sp_MoveDB
Go
Create Proc sp_MoveDB
(
@DataBase sysname,
@PathTo nvarchar ( 1024 )
)
As
Declare
@Sql nvarchar ( max ),
@AttachDatabase nvarchar ( max ),
@Error nvarchar ( 1024 ),
@Dir varchar ( 1024 ),
@ok int
If db_id ( @DataBase ) Is null
Begin
Set @Error = ' 错误的数据库名: ' + @DataBase
Raiserror 50001 @Error
Return
End
Set @Dir = ' Dir ' + @PathTo
Exec @ok = xp_cmdshell @Dir ,No_output
If @ok <> 0
Begin
Set @Error = ' 错误的文件路径: ' + @PathTo
Raiserror 50001 @Error
Return
End
Select @DataBase = name From sys.databases Where name = @DataBase
Print ' 数据库: ' + @DataBase + Char ( 13 ) + Char ( 10 ) + ' 正在移动数据库文件,请稍候 '
Select @Sql = Isnull ( @Sql + Char ( 13 ) + Char ( 10 ), '' ) + ' Kill ' + Rtrim (spid) From sys.sysprocesses Where db_name (dbid) = @DataBase
Exec ( @Sql )
Set @Sql = null
Select
@Sql = Isnull ( @Sql + Char ( 13 ) + Char ( 10 ), '' ) + ' Exec xp_cmdshell '' Move ' + physical_name + Char ( 32 ) + @PathTo +Right (physical_name, Charindex ( ' \ ' , Reverse (physical_name))) + ''' ,No_output ' ,
@AttachDatabase = Isnull ( @AttachDatabase + ' , ' , ' Create Database ' + Quotename ( @DataBase ) + ' On ' ) + ' (Filename= ''' + @PathTo +Right (physical_name, Charindex ( ' \ ' , Reverse (physical_name))) + ''' ) '
From sys.master_files Where database_id = db_id ( @DataBase )
Set @Sql = ' Exec sp_detach_db ' + Quotename ( @DataBase ) + Char ( 13 ) + Char ( 10 ) + @Sql + Char ( 13 ) + Char ( 10 ) + @AttachDatabase + ' For Attach '
Exec ( @Sql )
If @@Error = 0
Print ' 完成移动数据库文件. '
Else
Print ' 移动数据库文件失败. '
test:
Exec sp_MoveDB 'test1' ,'F:\SQL2005\test'
/*
数据库: test1
正在移动数据库文件,请稍候... ...
完成移动数据库文件.
*/
--移动所有的用户数据库文件如下:
Declare
@sql
nvarchar
(
max
)
Declare @Path nvarchar ( 1024 )
Set @Path = ' F:\SQL2005\test '
Select @sql = Isnull ( @sql + Char ( 13 ) + Char ( 10 ), '' ) + ' Exec sp_MoveDB ' + Quotename (name) + ' , ''' + @Path + ''''
From sys.databases where name Not in ( ' master ' , ' model ' , ' msdb ' , ' tempdb ' , ' ReportServer ' , ' ReportServerTempDB ' )
Exec ( @sql )
/*
数据库: deadlocktest
正在移动数据库文件,请稍候
完成移动数据库文件.
数据库: test1
正在移动数据库文件,请稍候
完成移动数据库文件.
*/
Declare @Path nvarchar ( 1024 )
Set @Path = ' F:\SQL2005\test '
Select @sql = Isnull ( @sql + Char ( 13 ) + Char ( 10 ), '' ) + ' Exec sp_MoveDB ' + Quotename (name) + ' , ''' + @Path + ''''
From sys.databases where name Not in ( ' master ' , ' model ' , ' msdb ' , ' tempdb ' , ' ReportServer ' , ' ReportServerTempDB ' )
Exec ( @sql )
/*
数据库: deadlocktest
正在移动数据库文件,请稍候
完成移动数据库文件.
数据库: test1
正在移动数据库文件,请稍候
完成移动数据库文件.
*/