日期:2014-05-18 浏览次数:20579 次
USE master GO DECLARE @DBName sysname, @DestPath varchar(256) DECLARE @DB table( name sysname, physical_name sysname) BEGIN TRY SELECT @DBName = 'FF', --input database name @DestPath = 'E:\' --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
sp_configure 'show advanced options',1 reconfigure go sp_configure 'xp_cmdshell',1 reconfigure go sp_configure 'show advanced options',0 reconfigure go USE master GO DECLARE @DBName sysname, @DestPath varchar(256) DECLARE @DB table( name sysname, physical_name sysname) BEGIN TRY SELECT @DBName = 'TMP_DB', --input database name @DestPath = 'd:\A-' --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 +