Archive

Posts Tagged ‘Move Master DB’

SQL Error – Windows could not start the SQL Server (MSSQLSERVER) on Local Computer (moved Master DB)

June 29, 2016 2 comments

 
I was trying to move my Master DBs to another folder by using steps mentioned in my one of [previous blog post]. But after all steps, when I tried to Start the services I got error with following popup:

Master DB move 01

Error Description:

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417.
 

–> So, I checked the error log file and it showed the reason for error.
Default location: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\

…
2016-06-27 12:18:28.02 Server The service account is ‘NT Service\MSSQLSERVER’. This is an informational message; no user action is required.
2016-06-27 12:13:33.30 Server Registry startup parameters:
     -d E:\SystemDatabases\Master\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l E:\SystemDatabases\Master\mastlog.ldf
2016-06-27 12:13:33.30 Server Command Line Startup Parameters:
     -s “MSSQLSERVER”
…
2016-06-27 12:13:33.41 spid5s [INFO] HkHostDbCtxt::Initialize(): Database ID: [1] ‘master’. XTP Engine version is 0.0.
2016-06-27 12:18:28.12 spid5s Starting up database ‘master’.
2016-06-27 12:18:28.13 spid5s Error: 17204, Severity: 16, State: 1.
2016-06-27 12:18:28.13 spid5s FCB::Open failed: Could not open file E:\SystemDatabases\Master\master.mdf for file number 1. OS error: 5(Access is denied.).
2016-06-27 12:18:28.13 spid5s Error: 5120, Severity: 16, State: 101.
2016-06-27 12:18:28.13 spid5s Unable to open the physical file “E:\SystemDatabases\Master\master.mdf”. Operating system error 5: “5(Access is denied.)”.
2016-06-27 12:18:28.13 spid5s Error: 17204, Severity: 16, State: 1.
2016-06-27 12:18:28.13 spid5s FCB::Open failed: Could not open file E:\SystemDatabases\Master\mastlog.ldf for file number 2. OS error: 5(Access is denied.).
2016-06-27 12:18:28.13 spid5s Error: 5120, Severity: 16, State: 101.
2016-06-27 12:18:28.13 spid5s Unable to open the physical file “E:\SystemDatabases\Master\mastlog.ldf”. Operating system error 5: “5(Access is denied.)”.
2016-06-27 12:18:28.13 spid5s SQL Server shutdown has been initiated
 

–> The above error log (in bold) tells that the service account NT Service\MSSQLSERVER does not have access to the files moved to new location.

–> So, to provide access, go to the new folder location –> Right click and select Properties –> Go to Security tab, click on Edit –> click on Add, to add the Service Account. Apply the Service Account (here in my case NT Service\MSSQLSERVER) and change the Locations to your PC, as its a local Service Account:

Master DB move 02

Give “Full Control”, and click OK on popups.
 

Now Start the “SQL Server” services and this time it will work.

To check and confirm the new file location of Master DB, Execute following SQL query:

USE master
GO

SELECT *
FROM sys.database_files

Master DB move 03
 

Advertisement
Categories: DBA Stuff, SQL Errors Tags:

SQL DBA – Move master Database to another drive – in simple steps

June 28, 2016 4 comments

 
Well, there are times when you want to move your master database from the default location to some other drive. Now this activity cannot be done with the normal ALTER DATABASE statement with MODIFY FILE option. And you need a spacial handling for this case of master DB.
 


 

–> Let’s first check the location of master DB:

USE master
GO

SELECT * FROM sys.database_files

Move Master 01
 

–> Now leave SSMS, and open SSCM i.e. SQL Server Configuration Manager. Here select “SQL Server Service”, and Rigth Click on the instance of SQL Server, and choose Properties. Now select the Startup Parameters tab.

Move Master 02

Here you will see 3 line items:

1. -d is the path of the master data file.

2. -e is the path of the SQL error log file.

3. -l is the path of the master log file.

So, you need to update the 1st and 3rd ones. As I want to move my files to E:\SystemDatabases\Master\ location, so you just need to replace the existing path with following:

1. master data File:
-dC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
-dE:\SystemDatabases\Master\master.mdf

2. master Log File:
-lC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-lE:\SystemDatabases\Master\mastlog.ldf
 

–> Now Stop the SQL Server services, by going to: RUN –> services.msc

–> Manually Copy the master.mdf & mastlog.ldf files to the new location

–> Start the SQL Server services.

–> To confirm the new location, just execute following query and check the path:

USE master
GO

SELECT * FROM sys.database_files

Master DB move 03
 

Categories: DBA Stuff Tags: