SQL Server 数据库文件修改存储位置

修改某个数据库的文件存储位置#

reference

https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-ver16

-- relocate database file
ALTER DATABASE testdb SET OFFLINE;  
GO  

ALTER DATABASE testdb -- database name
    MODIFY FILE ( NAME = testdb,   -- logic file name
                  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSRV2012\MSSQL\DATA\test\testdb.mdf');  -- New location
ALTER DATABASE testdb
    MODIFY FILE ( NAME = testdb_log,   -- logic file name
                  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSRV2012\MSSQL\DATA\test\testdb_log.ldf');  -- New location
GO  

ALTER DATABASE testdb SET ONLINE; 
GO

-- Return the logical file name.  
SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'testdb')  
    AND type_desc = N'LOG';  
GO  

修改全局数据库的文件存储位置#

reference

https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/view-or-change-the-default-locations-for-data-and-log-files?view=sql-server-ver16

更改默认位置之后，必须停止并重新启动 SQL Server 服务以完成更改