SQL Server does not start
On an experimental system, we attempted to create an issue. The issue is that SQL Server does not start. We stopped SQL Server and renamed model database’s data and log files. The result was this in error log file (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG).
2013-09-08 16:50:06.10 spid4s Starting up database 'msdb'. 2013-09-08 16:50:06.11 spid9s Starting up database 'mssqlsystemresource'. 2013-09-08 16:50:06.12 spid9s The resource database build version is 11.00.3000. This is an informational message only. No user action is required. 2013-09-08 16:50:06.15 spid9s Starting up database 'model'. 2013-09-08 16:50:06.16 spid9s Error: 5173, Severity: 16, State: 1. 2013-09-08 16:50:06.16 spid9s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. 2013-09-08 16:50:06.16 spid9s Error: 5173, Severity: 16, State: 1. 2013-09-08 16:50:06.16 spid9s One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. 2013-09-08 16:50:06.16 spid9s Log file 'E:\My Documents\SQL Server Data\modellog.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. 2013-09-08 16:50:06.16 spid9s Error: 945, Severity: 14, State: 2. 2013-09-08 16:50:06.16 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
The errorlog showed us that SQL Server could not find the data and log files and therefore SQL Server does not start. How do we fix this issue?
Fixing the issue of SQL Server not starting
If SQL Server was started, we could run the command below but the core issue is that SQL Server would not start.
alter database model modify file (name=modeldev, filename='C:\model2.mdf');
Command line comes to rescue.
- Run cmd.exe as Administrator
- cd c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
- type sqlservr.exe -c -m -T3608 -T4022
-c means start SQL Server but not as a service
-m means start the server under single user admin mode
-T3608 trace flag means prevent SQL Server from automatically starting and recovering any database except the master database
-T4022 trace flag means bypass automatically started (startup) procedures
At this point, only master database is available to us through SQL Server. Don’t use SSMS yet. Use command line. Open another command prompt (without Administrator mode) and let’s change the path of model database’s data and log files appropriately.
1> alter database model modify file (name=modellog, filename='C:\modellog2.ldf') 2> go 1 The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started. 1> alter database model modify file (name=modeldev, filename='C:\model2.mdf') 2> go 1 The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.
Wonderful. Now we have told master database that model can be found under c:\model2.mdf and c:\modellog2.ldf. Let’s start SQL Server and enjoy the clean startup!!