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

拷贝数据库文件

首先在本地运行如下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
WHERE type = 0;

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 + '.mdf', '''') + N' )';
    EXEC sp_executesql @sql;

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

CLOSE db_cursor;  
DEALLOCATE db_cursor;

分别迁移数据库文件和日志文件

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

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

重启服务

打开SQL Server 配置管理器，如果你更改了系统数据库的位置，需要在SQL server服务的属性中，更改启动参数，详情请参考官方文档：https://learn.microsoft.com/zh-cn/sql/relational-databases/databases/move-system-databases

重启服务

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