Change file location of Master Database

Change file location of Master Database.

To change file location of other database is very easy and it can be done in three steps.
Step1 Detach that database.
Step2 Cut from Old location and paste mdf and ldf files to new location.
Step3 Attach database with new location.

To change file location of master database is more tricky task, because you must need to restart the sql server service and need parameter changes in SQL Server Service property.

Please follow the steps to change database file location of master database.
Step1 : Check for the current path of master database files to copy from that path.

Select * FROM SYS.sysaltfiles where dbid = DB_ID('master')

Step2 : Script to change file location.

USE master;
GO
ALTER DATABASE master
MODIFY FILE (NAME = master, FILENAME = 'D:\Data\master.mdf');
GO
ALTER DATABASE master
MODIFY FILE (NAME = mastlog, FILENAME = 'D:\Log\mastlog.ldf');
GO

Step3 : Change SQL Server service parameter.
–> Goto SQL Server Configuration Manager –> Select you SQL instance(By Default MSSQLSERVER).
–> Right click on service and goto property.
–> Goto startup parameter tab and select “-dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf”
–> change it by “-dD:\DATA\master.mdf” and click on update.
–> Now Select “-lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf”
–> change it by “-lD:\Log\mastlog.ldf”
ChangeMasterFile1

Step4 : Stop your sql server instance copy mdf and ldf file from base location to new location and restart the service.

Step5 : If you get error like “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.”.

it means you don’t have rights on new locations.
ChangeMasterFile2

To solve this give a full permission to that folder.

–> Go to D:\Data –> Right click on Data Folder and go to Security tab.

–>Click on ADD button and add everyone user rights.

ChangeMasterFile3

 

 

Regards,

Nirav Gajjar