Sqlserver modifies the storage location of database files and log files

Time:2021-10-20
--View current storage location 
select database_id,name,physical_name AS CurrentLocation,state_desc,size from sys.master_files  
where database_ id=db_ ID (n 'database name'); 
 
--Modify the storage location of the file and it will take effect next time 
--Testdb is the database name, 
Alter database database name modify file (name = file name (excluding suffix), filename = 'file storage path'); 
Alter database database name modify file (name = file name (excluding suffix), filename = 'file storage path'); 
eg. 
  alter database testDb modify file ( name = testDb, filename = 'G:\SQL_DATA\testDb\testDb.mdf'); 
  alter database testDb modify file ( name = testDb_log, filename = 'G:\SQL_DATA\testDb\testdb_log.ldf'); 
 
--Modify the default database file storage location (effective immediately) 
EXEC xp_instance_regwrite  
@rootkey='HKEY_LOCAL_MACHINE',  
@key='Software\Microsoft\MSSQLServer\MSSQLServer',  
@value_name='DefaultData',  
@type=REG_SZ,  
@value='E:\MSSQL_MDF\data'  
GO  
--Modify the default log file storage location < span style = "font family: Arial, Helvetica, sans serif;" > (effective immediately)</span> 
EXEC master..xp_instance_regwrite  
@rootkey='HKEY_LOCAL_MACHINE',  
@key='Software\Microsoft\MSSQLServer\MSSQLServer',  
@value_name='DefaultLog',  
@type=REG_SZ,  
@value='E:\MSSQL_MDF\log'  
GO