[学习笔记]批量迁移用户数据库文件

拷贝数据库文件

首先在本地运行如下SQL语句，查看数据库文件的磁盘位置

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

默认是保存在C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA目录下

首先复制数据库文件到新的磁盘位置，比如E:\DATA\

更新索引

SQL server可以使用下面语句更改用户数据库的文件位置索引

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );

更多移动用户数据库内容请查看官方文档 https://learn.microsoft.com/zh-cn/sql/relational-databases/databases/move-user-databases

运行如下SQL语句

DECLARE @database_name NVARCHAR(128), @logical_name NVARCHAR(128), @sql NVARCHAR(MAX), @new_path NVARCHAR(256);

SET @new_path = N'E:\DATA\'; -- new file path

DECLARE db_cursor CURSOR FOR 
SELECT DB_NAME(database_id), name 
FROM sys.master_files;

OPEN db_cursor;  
FETCH NEXT FROM db_cursor INTO @database_name, @logical_name;  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    SET @sql = N'ALTER DATABASE ' + QUOTENAME(@database_name) + 
               N' MODIFY FILE ( NAME = ' + QUOTENAME(@logical_name, '''') + 
               N' , FILENAME = ' + QUOTENAME(@new_path + @logical_name, '''') + N' )';
    EXEC sp_executesql @sql;

    FETCH NEXT FROM db_cursor INTO @database_name, @logical_name;  
END; 

CLOSE db_cursor;  
DEALLOCATE db_cursor;

运行后会提示下次启动数据库时生效

注意，从 SQL Server 2008 R2 (10.50.x) 开始，全文目录已集成到数据库中，而不是存储在文件系统中。 现在移动数据库时将自动移动全文目录。
因此如果你的数据库是SQL Server 2008 R2(10.50.x) 之前的版本，你需要分别迁移数据库文件和日志文件

重启服务

再次查看sys.master_files内容，可以看到数据库文件已经迁移到新的磁盘位置

打开SQL Server 配置管理器，重启服务

删除之前的数据库文件，完成迁移