移动数据库物理文件

作者：csdn 网友：M1CR0S0FT
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 + ' 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