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