[回馈CSDN网友]贴个SQL小工具--移动数据库物理文件

USE master GO DECLARE @DBName sysname, @DestPath varchar ( 256 ) DECLARE @DB table ( name sysname, physical_name sysname) BEGIN TRY SELECT @DBName = ' FF ' , -- input database name @DestPath = ' E:\ ' -- input destination path -- kill database processes DECLARE @SPID varchar ( 20 ) DECLARE curProcess CURSOR FOR SELECT spid FROM sys.sysprocesses WHERE DB_NAME (dbid) = @DBName OPEN curProcess FETCH NEXT FROM curProcess INTO @SPID WHILE @@FETCH_STATUS = 0 BEGIN EXEC ( ' KILL ' + @SPID ) FETCH NEXT FROM curProcess END CLOSE curProcess DEALLOCATE curProcess -- query physical name INSERT @DB ( name, physical_name) SELECT A.name, A.physical_name FROM sys.master_files A INNER JOIN sys.databases B ON A.database_id = B.database_id AND B.name = @DBName WHERE A.type <= 1 -- set offline EXEC ( ' ALTER DATABASE ' + @DBName + ' SET OFFLINE ' ) -- move to dest path DECLARE @login_name sysname, @physical_name sysname, @temp_name varchar ( 256 ) DECLARE curMove CURSOR FOR SELECT name, physical_name FROM @DB OPEN curMove FETCH NEXT FROM curMove INTO @login_name , @physical_name WHILE @@FETCH_STATUS = 0 BEGIN SET @temp_name = RIGHT ( @physical_name , CHARINDEX ( ' \ ' , REVERSE ( @physical_name )) - 1 ) EXEC ( ' exec xp_cmdshell '' move " ' + @physical_name + ' " " ' + @DestPath + ' " ''' ) EXEC ( ' ALTER DATABASE ' + @DBName + ' MODIFY FILE ( NAME = ' + @login_name + ' , FILENAME = ''' + @DestPath + @temp_name + ''' ) ' ) FETCH NEXT FROM curMove INTO @login_name , @physical_name END CLOSE curMove DEALLOCATE curMove -- set online EXEC ( ' ALTER DATABASE ' + @DBName + ' SET ONLINE ' ) -- show result SELECT A.name, A.physical_name FROM sys.master_files A INNER JOIN sys.databases B ON A.database_id = B.database_id AND B.name = @DBName END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage END CATCH