1 USE master 2 GO 3 4 DECLARE 5 @DBName sysname, 6 @DestPath varchar(256) 7 DECLARE @DB table( 8 name sysname, 9 physical_name sysname) 10 11 12 BEGIN TRY 13 14 SELECT 15 @DBName = 'TargetDatabaseName', --input database name 16 @DestPath = 'D:\SqlData\' --input destination path 17 18 19 -- kill database processes 20 DECLARE @SPID varchar(20) 21 DECLARE curProcess CURSOR FOR 22 23 SELECT spid 24 FROM sys.sysprocesses 25 WHERE DB_NAME(dbid) = @DBName 26 27 OPEN curProcess 28 FETCH NEXT FROM curProcess INTO @SPID 29 WHILE @@FETCH_STATUS = 0 30 BEGIN 31 EXEC('KILL ' + @SPID) 32 FETCH NEXT FROM curProcess 33 END 34 CLOSE curProcess 35 DEALLOCATE curProcess 36 37 -- query physical name 38 INSERT @DB( 39 name, 40 physical_name) 41 SELECT 42 A.name, 43 A.physical_name 44 FROM sys.master_files A 45 INNER JOIN sys.databases B 46 ON A.database_id = B.database_id 47 AND B.name = @DBName 48 WHERE A.type <=1 49 50 --set offline 51 EXEC('ALTER DATABASE ' + @DBName + ' SET OFFLINE') 52 53 --move to dest path 54 DECLARE 55 @login_name sysname, 56 @physical_name sysname, 57 @temp_name varchar(256) 58 DECLARE curMove CURSOR FOR 59 SELECT 60 name, 61 physical_name 62 FROM @DB 63 OPEN curMove 64 FETCH NEXT FROM curMove INTO @login_name,@physical_name 65 WHILE @@FETCH_STATUS = 0 66 BEGIN 67 SET @temp_name = RIGHT(@physical_name,CHARINDEX('\',REVERSE(@physical_name)) - 1) 68 EXEC('exec xp_cmdshell ''move "' + @physical_name + '" "' + @DestPath + '"''') 69 EXEC('ALTER DATABASE ' + @DBName + ' MODIFY FILE ( NAME = ' + @login_name 70 + ', FILENAME = ''' + @DestPath + @temp_name + ''')') 71 FETCH NEXT FROM curMove INTO @login_name,@physical_name 72 END 73 CLOSE curMove 74 DEALLOCATE curMove 75 76 -- set online 77 EXEC('ALTER DATABASE ' + @DBName + ' SET ONLINE') 78 79 -- show result 80 SELECT 81 A.name, 82 A.physical_name 83 FROM sys.master_files A 84 INNER JOIN sys.databases B 85 ON A.database_id = B.database_id 86 AND B.name = @DBName 87 END TRY 88 BEGIN CATCH 89 SELECT ERROR_MESSAGE() AS ErrorMessage 90 END CATCH 91 GO
注：原文摘自：Aaron的博客：http://www.cnblogs.com/aarond/archive/2013/03/29/2988620.html
