Cannot connect to SQL Server, cannot start SQLEXPRESS service

Question

There are 2 issues at the same time.

1. I cannot connect to the database (SQL Server 2012) using SSMS - I'm getting error 1225
2. I cannot start the SQLEXPRESS service - getting error 3414

Earlier last week, I had connected to the server and found that the database was in Suspect mode. So, I fixed it by changing it to Emergency, then DBCC, then Single User and then multi user which worked fine and we could access the data in the app as well.

Now after 4 days this is a new issue. When I looked online the solution to error 1225 is to start the SQLEXPRESS Service on which I am getting error 3414, the solution to which is to repair the database which I cannot connect to. As far as I know, the SQLEXPRESS service error 3414 is the main issue here.

Note: I have admin rights. I have tried with both Windows auth and SQL Server auth. I am selecting the correct server name.

Please help or share an article(s) on how to fix the issues. Is there any way I can access the database and see if it's in SUSPECT/EMERGENCY mode or something and maybe repair it again.

Error #1225:

Error #3414:

Update 1 [9 June]:

Event Logs - Error: The log scan number (94:280:1) passed to log scan in database 'model' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

SQL Error Log

Answer 1

One of your system databases is corrupt. This is preventing your instance from starting. So you are going to have to rebuild the system databases.

If you have backups of your user databases and system datbases (you do have backups, don't you?), then you can follow any one of the following steps outlined in the different articles.

Rebuild System Databases (Miccrosoft | SQL Docs)

The prerequisites assume you can still access the instance, which doesn't seem to be possible in your case. Assuming you do have backups of the master, msdb and model databases, you could skip directly to the section Rebuild System Databases

1. Insert the SQL Server installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016.

2. From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.

    Setup /QUIET /ACTION=REBUILDDATABASE 
    /INSTANCENAME=InstanceName 
    /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] 
    [/SQLCOLLATION=CollationName]
   

3. When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages. Examine the Summary.txt log file to verify that the process completed successfully. This file is located at C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs.

4. RebuildDatabase scenario deletes system databases and installs them again in clean state. Because the setting of tempdb file count does not persist, the value of number of tempdb files is not known during setup. Therefore, RebuildDatabase scenario does not know the count of tempdb files to be re-added. You can provide the value of the number of tempdb files again with the SQLTEMPDBFILECOUNT parameter. If the parameter is not provided, RebuildDatabase will add a default number of tempdb files, which is as many tempdb files as the CPU count or 8, whichever is lower.

How to Restore Corrupted Model Database From Backup? (SQL Authority)

Pinal Dave has a blog article SQL SERVER - How to Restore Corrupted Model Database From Backup? in which he discusses how to restore a corrupt model database. It references the default way of restoring the system database (see the Microsoft Article), but supplies an alternative solution of copying a model database's database files from another installation to you installation.

Repair Installation

Have you tried running the Setup Program from Programs and Features and clicking on the repair option?

1. Windows key
2. Start typing appwiz.cpl
3. Open Programs and Features
4. Locate and start the Microsoft SQL Server 2012 or Microsoft SQL Server 2012 (64-bit)program.
5. Enter credentials for escalated execution, if required.
6. Click on the Repair option.
7. Locate the installation media (CD-ROM,Mounted ISO file, Unpacked ZIP)
   • The folder just above the setup.exe for your version of SQL Server Express
8. Click through the steps and see if the repair works.

Backup / Restore

It's always a good idea to have a backup of your databases (SYSTEM and USER) in order to prevent such situations. A good starting point is Ola Hallengren's SQL Server Maintenance Solution. A quick and free way to back up and optimize your if you're on a tight budget.

Good luck.