Share via

SQL Server: Move master database to another location (drive)

  • Article

https://msdnshared.blob.core.windows.net/media/2016/08/0841.NinjaAwardTinySilver.png

Problem Description

Sometimes master files cannot be kept on OS drive or format current drive for different reason.. DBA needs to move these files to another location.

Troubleshooting Steps

1. Check master files in C drive:

Open SSMS and run following command:

 

      SELECT * FROM master.sys.database_files

2. Check temp files using configuration manager

Right-click ->MSSQL server > Select startup parameter where see LDF, MDF location.

3. Stop SQL Services and copy master mdf and ldf file to new location.

Stop SQL services using services.msc only.

Note: If we stop SQL services then copy the master file.

Copy master ldf and mdf in E drive masterdb folder and give proper permissions to the folder.

Rename mdf and ldf older files

4. Update new master ldf and mdf in configuration manager and start SQL Services using services.msc.

Go to SQL server configuration manager > select startup parameter then update master ldf and mdf new path

Start SQL services using services.msc

5. Check master mdf and ldf location using SSMS.

Open SSMS and run following command:



      SELECT * FROM master.sys.database_files

Troubleshooting

An error occurred during activity:

Do not start SQL services from configuration manager. Error will show following.

File not present or permission error. You need to check in Event Viewer for the application.

Note: Make sure that permission has full access to folder and files must present in the specified location.

Note: Allocation unituses 64K for the best performance.

References

SQL DBA – Move master Database to another drive – in simplesteps

See also

sql server configuration manager remote procedure call failed(0x800706be)

use 64K for the best performance.