移動Sql Server資料庫的指令碼

前段時間做過這麼一件事情，把原本放在c盤的所有資料庫（除了sql server系統檔案外）檔案Move到D盤，主要是為了方便後續管理以及減少磁碟I/O阻塞（C,D是2個獨立磁碟）。

指令碼很簡單，在此只是記錄下來，以免以後忘記，也方便複用：

這個指令碼只有2個參數需要輸入，即：目標資料庫名字和目標目錄

 

USE masterGODECLARE    @DBName sysname,    @DestPath varchar(256)DECLARE @DB table(    name sysname,    physical_name sysname)BEGIN TRYSELECT    @DBName = 'TargetDatabaseName',   --input database name    @DestPath = 'D:\SqlData\'         --input destination path-- kill database processesDECLARE @SPID varchar(20)DECLARE curProcess CURSOR FORSELECT spidFROM sys.sysprocessesWHERE DB_NAME(dbid) = @DBNameOPEN curProcess    FETCH NEXT FROM curProcess INTO @SPID    WHILE @@FETCH_STATUS = 0    BEGIN            EXEC('KILL ' + @SPID)            FETCH NEXT FROM curProcess    ENDCLOSE curProcessDEALLOCATE curProcess-- query physical nameINSERT @DB(    name,    physical_name)SELECT    A.name,    A.physical_nameFROM sys.master_files AINNER JOIN sys.databases B    ON A.database_id = B.database_id        AND B.name = @DBNameWHERE A.type <=1--set offlineEXEC('ALTER DATABASE ' + @DBName + ' SET OFFLINE')--move to dest pathDECLARE    @login_name sysname,    @physical_name sysname,    @temp_name varchar(256)DECLARE curMove CURSOR FORSELECT    name,    physical_nameFROM @DBOPEN 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        ENDCLOSE curMoveDEALLOCATE curMove-- set onlineEXEC('ALTER DATABASE ' + @DBName + ' SET ONLINE')-- show resultSELECT    A.name,    A.physical_nameFROM sys.master_files AINNER JOIN sys.databases B    ON A.database_id = B.database_id        AND B.name = @DBNameEND TRYBEGIN CATCH    SELECT ERROR_MESSAGE() AS ErrorMessageEND CATCHGO