June 7, 2011 at 8:36 am
Hi,
I am getting the below error while starting the sql server 2005. Please let me know how to restore the model database or please direct to any blogs\articles where i can find the steps.
I have tried starting the server in single user mode, no luck.
2011-06-07 19:54:28.09 spid5s Starting up database 'master'.
2011-06-07 19:54:28.34 spid5s 1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2011-06-07 19:54:28.42 spid5s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2011-06-07 19:54:28.42 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2011-06-07 19:54:28.59 spid5s SQL Trace ID 1 was started by login "sa".
2011-06-07 19:54:28.67 spid5s Starting up database 'mssqlsystemresource'.
2011-06-07 19:54:28.70 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2011-06-07 19:54:29.64 spid5s Server name is 'NAVEEN'. This is an informational message only. No user action is required.
2011-06-07 19:54:29.65 spid9s Starting up database 'model'.
2011-06-07 19:54:29.81 spid9s Error: 5173, Severity: 16, State: 1.
2011-06-07 19:54:29.81 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.
2011-06-07 19:54:29.84 spid9s Error: 5173, Severity: 16, State: 1.
2011-06-07 19:54:29.84 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.
2011-06-07 19:54:29.87 spid9s Log file 'D:\Program Files\MSSQL2005\MSSQL.1\MSSQL\DATA\modellog.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
2011-06-07 19:54:29.90 spid9s Error: 945, Severity: 14, State: 2.
2011-06-07 19:54:29.90 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-06-07 19:54:29.92 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2011-06-07 19:54:29.92 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Regards,
Naveen
June 7, 2011 at 8:41 am
Were files changed after the shutdown? Or have you confirmed corruption is the issue already?
You should be able to restore the model database from a backup. Do you have backups of your system databases?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 9:29 pm
Hi,
I have the good full backup of system databases and user databases.
This is what happened...
1. By mistake moved the log file of model database to another drive when the sql server was shutdown
2. Started the sql server... SQL Server did not start as it was trying to find the log file of model db at the location D:\Program Files\MSSQL2005\MSSQL.1\MSSQL\DATA\modellog.ldf
3. Then i have moved the log file of model database back to the location D:\Program Files\MSSQL2005\MSSQL.1\MSSQL\DATA\modellog.ldf
4. Started the sql server ... Getting the error posted
I am not sure on the next steps.
Do i need to rebuild all the system databases and then restore the databases with recent backups?
or
Can i rebuild and restore only model database? and how to do that
June 8, 2011 at 6:33 am
Start with restoring model. See if that solves it for you.
If the service won't start because it can't create tempdb, you may need to repair the installation. There are instructions on MSDN for how to do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2011 at 8:33 am
Hi ..
Thanks.
I am not sure how to restore the model database here. I am unable to start the sql server in single user mode. Do i need to uninstall and install sql server, then restore the system databases with recent backups?
Please guide me to right forum or link... I have tried to find the solution in internet. I could not find the solution to restore the model database alone..
Regards,
Naveen
June 12, 2011 at 9:44 am
Firstly, are you sure you copied back the correct log file?
If you have another instance at exactly the same version as this you could use a COPY of the model database files from that instance to get this one going. If you have made changes to the model you would then be able to restore from backup.
there is a workaround here using the tempdb database files. I have never tried this myself but Perry knows his stuff.
Otherwise, try starting SQL in master only recovery mode. From the command line
NET START MSSQLSERVER /f /T3608
then restore the model database from either SQLCMD or SSMS that open in query window only
stop SQL - NET STOP MSSQLSERVER
and then restart in the normal way
---------------------------------------------------------------------
June 12, 2011 at 10:40 pm
Hi...
Yes. I have copied the correct .ldf file back to original location. I do not have another instance of sql server on the box.
I have tried to start the sql server in single user mode.
D:\Program Files\MSSQL2005\MSSQL.1\MSSQL\Binn>NET START MSSQLSERVER /f /T3608
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.
Then tried to restore model database, but getting the below error.
restore database model from disk ='G:MSSQL2005\Backup\model\model_backup_201106051920.bak'
Error:
Msg 3112, Level 16, State 1, Line 1
Cannot restore any database other than master when the server is in single user mode.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Regards,
Naveen
June 12, 2011 at 10:57 pm
I have tried another option using the tempdb data and log files as per the link (http://www.sqlservercentral.com/Forums/Topic1054581-1550-1.aspx). I am unable to start the sql service, getting the below errors in event viewer...
---------------------------------------
Could not redo log record (33:16:298), for transaction ID (0:0), on page (1:20), database 'model' (database ID 3). Page: LSN = (26:384:2), type = 1. Log: OpCode = 18, context 2, PrevPageLSN: (33:16:286). Restore from a backup of the database, or repair the database.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
----------------------------------------
During redoing of a logged operation in database 'model', an error occurred at log record ID (33:16:298). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
----------------------------------------------------
June 13, 2011 at 3:39 am
I recovered model when I lost a log drive by
The detach/attach steps seemed to rectify the problems. Microsoft document a way of moving the system database files that uses this method.
June 14, 2011 at 9:03 am
Hi...
I did not have another instance of SQL Server. So I have installed named instance and copied the data & log files of model database. Started the default instance of SQL Server normally and it started successfully. Then restored model database from recent backup.
One observation:
My Default instance build is 9.0.3042 (developer edition)
Named instance build is 9.0.1399 (developer edition)
I have used the data & log files of model database in named instance (9.0.1399) to start the SQL Server (default instance - 9.03.3042). But in the previous post you have mentioned that the SQL Server build must be same. You meant edition of SQL Server or build version ???
Regards,
Naveen
June 14, 2011 at 9:44 am
krn045 (6/14/2011)
Started the default instance of SQL Server normally and it started successfully. Then restored model database from recent backup....
I have used the data & log files of model database in named instance (9.0.1399) to start the SQL Server (default instance - 9.03.3042). But in the previous post you have mentioned that the SQL Server build must be same. You meant edition of SQL Server or build version ???
I meant Build: the system databases are changed by updates (service packs and CUs), so the Build ought to be identical.
I suspect that if all you used was model, and you have now restored a backup from the default instance, you should be fine. The experts might recommend reapplying the updates that took your default instance to 3042 (I am assuming you mean 9.00.3042), but I think it would be overkill in this case. But 3042 is not ideal - it was replaced by 3043, and is now a long way behind many useful fixes. I would at least go to SP4 if you have the opportunity, though testing and validation overheads may be stopping you.
June 14, 2011 at 10:22 am
Having now restored a backup of the model database at the correct version I would now restart SQL again just to be sure.
Did you not get the model db files I sent you in a PM?
---------------------------------------------------------------------
June 15, 2011 at 9:12 am
@ Ewan Hampson
Yes I meant 9.00.3042. We have already planned to apply Service Pack 4 on all the SQL Server 2005 instances.
@ george sibbald
I have not received the files you have sent. I have restarted the sql instance and no issues. Now my sql instance is up.
@SSC
Thank you all for help and guidance.
June 15, 2011 at 9:29 am
To avoid this problem in the future, next time your instance is down take a flat file copy of the system database files to another directory (including the resource database). do this after every SQL upgrade.
Then if the system dbs get corrupted you just need to slide the copies into place.
---------------------------------------------------------------------
June 20, 2011 at 5:39 am
george sibbald (6/15/2011)
To avoid this problem in the future, next time your instance is down take a flat file copy of the system database files to another directory (including the resource database). do this after every SQL upgrade.Then if the system dbs get corrupted you just need to slide the copies into place.
I wish it worked that easily, George. But the last time I checked, Master, at the very least, requires more hoops than that.
Have you actually tested this approach?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply