SQLServer移动ALWASYON副本文件的方法和脚本

1.暂停ALWAYSON数据传送

ALTER DATABASE [db1] SET HADR SUSPEND;

ALTER DATABASE [db2] SET HADR SUSPEND;

ALTER DATABASE [db3] SET HADR SUSPEND;

2.生成脚本：

SELECT  database_id,

(sum(size)*8/1024/1024) as usedGb

FROM    sys.master_files 

WHERE   database_id in (

select database_id from sys.databases 

where

 NAME NOT IN ('....')

)  

AND physical_name LIKE 'E:\%' group  by database_id

order by  2 desc

3.生成脚本2

SELECT  name , 

        physical_name AS CurrentLocation ,

        state_desc,(size*8/1024/1024) as usedGb

,'ALTER DATABASE ['+DB_NAME(database_id)+'] MODIFY FILE ( NAME = '+name+' , FILENAME = '''+REPLACE(physical_name,'D:\','F:\')+''' )' 

FROM    sys.master_files 

WHERE   database_id in (17)  

AND  SIZE>1024*1024  AND physical_name LIKE 'E:\%' order by database_id

4.停SQLSERVER服务

4.1.剪切文件到新位置

4.2.启动SQLSERVER服务

5.恢复ALWAYSON数据传送

ALTER DATABASE [bdg_sub2] SET HADR RESUME;