SQL SERVER 如何修改数据库文件路径

时间:2019-08-13 19:12:06   收藏:0   阅读:527

原文地址: https://blog.csdn.net/baomfeng/article/details/84031971

DECLARE @DBNAME VARCHAR(255)
 DECLARE @TargetPath VARCHAR(255)
 DECLARE @CmdCommand VARCHAR(2000)

SET @DBNAME=GTN_New2019SET @TargetPath=D:\MyDB--第一步:设置数据库脱机
SET @CmdCommand= ALTER DATABASE +@DBNAME+ SET OFFLINEEXEC(@CmdCommand)

--第二步:物理拷贝数据库文件到新目录
DECLARE @FileName VARCHAR(255)
 DECLARE @SourceFullName VARCHAR(255)
 DECLARE FileCur CURSOR for SELECT name,physical_name from sys.master_files where database_id=db_id(@DBNAME)
 OPEN FileCur
 FETCH NEXT FROM FileCur INTO @FileName,@SourceFullName
 WHILE @@FETCH_STATUS=0
 BEGIN
  SET @CmdCommand= copy "+@SourceFullName+" "+@TargetPath+"EXEC master..xp_cmdshell @CmdCommand
  
  --修改数据库文件的路径指向新目录
 SET @CmdCommand=ALTER DATABASE +@DBNAME+ MODIFY FILE(FILENAME=‘‘‘+@TargetPath+CASE WHEN RIGHT(@TargetPath,1)=\THEN‘‘ ELSE\END+
   RIGHT(@SourceFullName, CHARINDEX(\‘, REVERSE(@SourceFullName))-1)+‘‘‘,name=‘‘‘+@FileName+‘‘‘)EXEC(@CmdCommand)
  FETCH NEXT FROM FileCur INTO @FileName,@SourceFullName
 END
 CLOSE FileCur
 DEALLOCATE FileCur

--第三步:设置数据库联机
SET @CmdCommand= ALTER DATABASE +@DBNAME+ SET ONLINEEXEC(@CmdCommand)

原文:https://www.cnblogs.com/juneyss/p/11347392.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!