Sqlserver移动文件路径move datafile的三种方法

sqlserver移动文件的三种方法

1、设置数据库分离，再把文件拷贝到新目录，再附加

2、设置数据库脱机，再把文件拷贝到新目录，

再ALTER DATABASE XX MODIFY FILE (NAME='logicalname',FILENAME='新目录\YY.dbf');

再联机

3、ALTER DATABASE XX MODIFY FILE (NAME='logicalname',FILENAME='新目录\YY.dbf');

再关闭实例，再拷贝文件到新目录，再启动实例

其中查询NAME和FILENAME的语句，假设数据库名为XX

SELECT name,physical_name FROM master.sys.master_files WHERE database_id =DB_ID(N'XX');

如果文件类型是filestream，则上面FILENAME是目录(如下D:\FS1.FILESTREAM\Netfile_FS1是目录)

ALTER DATABASE Netfile1 MODIFY FILE (NAME = 'Netfile_FS1', FILENAME = 'D:\FS1.FILESTREAM\Netfile_FS1'); 

注意事项

1、always on的主副本不适用以上方法1、2，只能适用以上方法3

2、always on的辅助副本不适用以上方法1、2、3，需要在方法3的基础上做特殊处理

3、mirror的主库、从库都不适用以上方法1、2，只能适用以上方法3

4、logshipping的主库适用以上方法1、2、3

5、logshipping的从库不适用以上方法1、2，只能适用以上方法3

设置数据库脱机时，先执行如下，查看是否有事务，有事务的话，要不杀掉，要么等事务执行完毕
DBCC OPENTRAN (dbname)
或
select * from sys.sysprocesses where dbid=db_id('dbname')