sqlserver数据库移动数据库路径的脚本示例

复制代码代码如下:
USE master
GO

DECLARE
    @DBName sysname,
    @DestPath varchar（256）
DECLARE @DB table（
    name sysname,
    physical_name sysname）

BEGIN TRY

SELECT
@DBName = "TargetDatabaseName", --input database name
@DestPath = "D:SqlData" --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
GO