程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> sqlserver數據庫挪動數據庫途徑的劇本示例

sqlserver數據庫挪動數據庫途徑的劇本示例

編輯:MSSQL

sqlserver數據庫挪動數據庫途徑的劇本示例。本站提示廣大學習愛好者:(sqlserver數據庫挪動數據庫途徑的劇本示例)文章只能為提供參考,不一定能成為您想要的結果。以下是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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved