前段时间做过这么一件事情，把原本放在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