sqlserver數據庫移動數據庫路徑的腳本示例

      前段時間做過這麼一件事情，把原本放在c盤的所有數據庫（除了sql server系統文件外）文件Move到D盤，主要是為了方便後續管理以及減少磁盤I/O阻塞（C,D是2個獨立磁盤）。腳本需輸入2個參數：目標數據庫名字和目標目錄

  代碼如下: 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