0

Please can I ask for your assistance in investigating as to why I cannot restore the master db from a recent backup?

Physical copies taken of .mdf and .ldf master files (just in case!) and then I've recreated a corrupted master database by manually deleting content from the .mdf file using Notepad to simulate disk corruption.

Subsequently, SQL Server (MSSQLSERVER) service will not start which is the scenario I'm trying to resolve.

Actions taken to backup Master database:

Prior to corrupting master, a backup was taken using following command:

BACKUP DATABASE master 
TO DISK = 'C:\Temp\MasterBK.DAT' 
WITH FORMAT, NAME = 'Full Backup of master'

Actions taken to restore:

Various different attempts to start SQL Servre service in single-user mode, including:

-mSQLCMD 
-m"SQLCMD"
sqlservr.exe -m
NET START MSSQLSERVER /m

Tried adding -m into Startup Parameters in SQL Server Configuration Manager

Error received on restore

-mSQLCMD is not a recognised as an internal or external command
Service did not start in a timely manner

Version Information:

[Windows XP]

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
L.Kibbey
  • 1

2 Answers2

0

  1. Please confirm if your backup is right, and then delete the current database (the one which is corrupted).

  2. Restore DB using its .LDF and .MDF physical files:

    CREATE DATABASE DIS 
   ON (FILENAME = 'C:\DIS.mdf'), 
      (FILENAME = 'C:\DIS_1.LDF') 
   FOR ATTACH;

Please give a try.

For service not running, try to open windows services and look for your SQL Server service there or just go SQL Server Configuration Manager.

To run sql server in single user mode, check this post https://mssqlfun.com/2014/09/04/how-to-start-sql-server-in-single-user-mode/.

Your cmd commands might not work because you're not on the right directory where this executables resides.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anonymous Duck
  • 2,942
  • 1
  • 12
  • 35
  • Hi, we are unable to start (MSSQLSERVER) service in single user mode in order to run the restore SQL commands. We have tried adding '-m;' into the Startup Parameters field. Starting the service following adding the -m results in the error 'service did not start in a timely manner'. Any ideas? – L.Kibbey Jun 15 '16 at 13:10
  • @L.Kibbey any errors prompted when trying to access it? http://stackoverflow.com/questions/10708993/cant-start-sql-server-in-single-user-mode try also this commands to force the services – Anonymous Duck Jun 15 '16 at 14:33
0

I think this may help you go through this link https://campus.barracuda.com/product/backup/article/BBS/RestoreMSSQL2008MasterDB/?welcome-to-campus=techlibrary

Ruhaan
  • 176
  • 13
  • This is a comment, not an answer in the current form. Please avoid posting link-only answers, include the essentials/important parts from the linked resources too. Remote resources are not necessary remain available in the future. Please read [answer] in [help]. – Pred Jun 15 '16 at 11:12
  • @Pred, I'll try to answer properly next time. – Ruhaan Jun 15 '16 at 11:15