There are 2 issues at the same time.
- I cannot connect to the database (SQL Server 2012) using SSMS – I'm getting error 1225
- 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.
Best Answer
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
andmodel
databases, you could skip directly to the section Rebuild System DatabasesHow 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?
appwiz.cpl
Programs and Features
Microsoft SQL Server 2012
orMicrosoft SQL Server 2012 (64-bit)
program.Repair
option.setup.exe
for your version of SQL Server ExpressBackup / 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.