SQL小工具--移动数据库物理文件

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go
sp_configure 'show advanced options',0
reconfigure
go



USE master
GO

DECLARE    
    @DBName sysname,
    @DestPath varchar(256)
DECLARE @DB table(
    name sysname,
    physical_name sysname)
 

BEGIN TRY

SELECT 
    @DBName = 'TMP_DB',   --input database name
    @DestPath = 'd:/A' --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

消息：

SQL code

配置选项 'show advanced options' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。
配置选项 'xp_cmdshell' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。
配置选项 'show advanced options' 已从 1 更改为 0。请运行 RECONFIGURE 语句进行安装。

(6 行受影响)

(2 行受影响)
文件 'Tmp_Db_dat' 在系统目录中已修改。新路径将在数据库下次启动时使用。

(2 行受影响)
文件 'Tmp_Db_log' 在系统目录中已修改。新路径将在数据库下次启动时使用。

(2 行受影响)
文件 'Tmp_Db_FILEGROUP_1' 在系统目录中已修改。新路径将在数据库下次启动时使用。

(2 行受影响)
文件 'Tmp_Db_FILEGROUP_2' 在系统目录中已修改。新路径将在数据库下次启动时使用。

(2 行受影响)
文件 'Tmp_Db_FILEGROUP_3' 在系统目录中已修改。新路径将在数据库下次启动时使用。

(2 行受影响)
文件 'Tmp_Db_FILEGROUP_4' 在系统目录中已修改。新路径将在数据库下次启动时使用。