Restoring SQL Server Master Database

CHECK THE STATUS OF APPLICATION SERVICES

STOP SQL SERVER SERVICES

Figure 1. Stopping SQL Services

START SQL SERVER IN THE SINGLE USER MODE

  1. Open CMD Prompt
  2. Navigate to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
  3. Issue sqlservr –m OR NET start MSSQLServer /c /m /T3604
Figure 2. Starting SQL Server in Single-user Mode
Figure 3. Starting SQL Server in Single-user Mode
Figure 4. Checking the SQL Server Install Path

RESTORE MASTER

  1. Open SQL Server Management Studio
  2. Run the code in Listing 1 in SQL Server Management Studio
-- Listing 1: Restore Master Database
restore database master from disk ='<full_path_of_backup>' with replace;
Figure 5. Restoring Master

SERVICE ACCOUNT CHANGE

  1. Change the SQL Server Service Account to the currently logged on account.
  2. Open Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager
  3. Right-Click each service and select Properties
  4. In the Log On tab, enter the desired Account Name and the corresponding password.
Figure 6. Changing Service Account

SERVER NAME

-- Listing 2: Change the Instance Name for a SQL Server Instance
-- Check the current server name
select @@SERVERNAME-- Change the server name as seen by the databasesp_dropserver 'EUK-POSTSVR-01'--Present Server name goes here
go
sp_addserver 'EUK-POSTBKP-01','local'--New Server name goes here
go

SERVICE LOGINS

-- Listing 3: Remove Unnecessary Logins
drop login [EUK-POSTSVR-01\SQLServer2005MSSQLUser$EUK-POSTSVR-01$MSSQLSERVER]
drop login [EUK-POSTSVR-01\SQLServer2005MSFTEUser$EUK-POSTSVR-01$MSSQLSERVER]
drop login [EUK-POSTSVR-01\SQLServer2005SQLAgentUser$EUK-POSTSVR-01$MSSQLSERVER]
-- Listing 4: Add local default SQL Server Groups
create login [EUK-POSTBKP-01\SQLServer2005MSSQLUser$EUK-POSTBKP-02$MSSQLSERVER] from windows;
create login [EUK-POSTBKP-01\SQLServer2005SQLAgentUser$EUK-POSTBKP-02$MSSQLSERVER] from windows;
create login [EUK-POSTBKP-01\SQLServer2005MSFTEUser$EUK-POSTBKP-02$MSSQLSERVER] from windows;
Table 1. OS Privileges Required by SQL Server Service Accounts

CONCLUSIONS

Figure 7. Checking Permission Issues with ProcMon

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
{coding}Sight

{coding}Sight

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/