Restoring Master database on MS SQLExpress
Today all of a sudden, one of my MsSQLExpress servers refused to start. On looking at the Windows Event Log, I discovered the following errors:
FCB::Open failed: Could not open file c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQ\DATA\mastlog.ldf for file number 2. OS error: 2(The system cannot find the file specified.)/
FCB::Open: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf’. Diagnose and correct the operating system error, and retry the operation.
I wanted to find a way to quickly restore the Master database. Unfortunately I did not have any backup. (This was a playground server, not a production box). Since I had nothing changed on the master db, restoring a fresh copy would not make any difference.
Luckily I found out that there is a directory called “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data” and as the name suggests it contains a template DB. So I simply replaced master.mdf and mastlog.ldf files in “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data” directory with those found under the Template Data directory.
Hurray! My MsSQLExpress server started fine without any further complains.