前段時間做過這麼一件事情，把原本放在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
