SQL语句实现移动数据库文件
看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
正在移动数据库文件,请稍候
完成移动数据库文件.
*/