Dear all,

I experienced a few problems over the weekend which may be specific to our setup, but thought the information may be useful if anyone's in a similar situation.  This solution is not approved/endorsed in any way, and as ever, you complete it at your own risk.  Usual warning - back up your .mdf, .ldf and .bak files somewhere completely different before starting out with this.

Environment:

Windows Server 2003 R2 Standard 32-bit edition, CELCAT version 6.3.0.40, MSDE 2000 Release A.

Note: This will shortly become a legacy version of CELCAT and we're moving away from this version as soon as we possibly can, following end-of-life announcements for MSDE.

Symptoms:

1. The service MSSQLSERVER hangs on starting with Event ID 7022, reported by Service Control Manager.
2. A few minutes later, an event may be logged as terminating unexpectedly "The MSSQLSERVER service terminated unexpectedly. It has done this (x) time(s)." with Event ID: 7034, again reported by Service Control Manager
3. The SQL icon appears in the system tray but with no green "run" icon.
4. If you open SQL Server Service Manger then you cannot connect to your server.
5. If you try to uninstall MSDE you receive the message "The instance name specified is invalid" and then MSDE disappears from "Add/Remove Programs"
6. Nothing will connect to the "master" database, and the first thing you might know is client users ringing you with this error message (because CT Server is still working, and provided MSDTC is still working properly then the problem lies with MSDE)

Possible cause:

1. It may be coincidence but this happened with the latest Windows Update for Microsoft Silverlight (KB982926).
2. The update doesn't complete and forces Windows to reboot, terminating MSDE unexpectedly
3. Upon restart, you get the "This system has recovered from a serious error" message.
4. The update is still trying to complete in the background.
5. No matter what you try starting the SQL Server service results in "sqlmangr.exe" appearing in your task manager but never starting successfully.
6. If you end sqlmangr.exe and try to start it manually from services.mmc then you get the error 1053.

The solution:

1. Backup all your .mdf, .ldf and .bak files somewhere completely different (take a copy of the Binn and Data folders, in a default instance at C:\Program Files\Microsoft SQL Server\MSSQL)
2. Follow the instructions very carefully at: http://support.microsoft.com/kb/320873 - install the SDK (or find your original CD, as it's a massive install for a command line tool)
3. You will need to use msizap.exe to remove traces of MSDE, and then edit the registry, then remove the Binn and Data files (which is why you backed them up!)
4. Restart your server
5. Install MSDE again from scratch, and either put an InstanceName in the [Options] of setup.ini or leave to default - either way, make sure this is the same as your initial installation.
6. Restart your server again.
7. If the above has gone well, you should see the SQL Server icon with the green "run" icon in your system tray, although there are no databases yet.
8. Copy just your timetable files (not the master, or anything else) from your backup location to C:\Program Files\Microsoft SQL Server\MSSQL\Data - For example, a timetable will have two files: mytimetable.mdf and mytimetable.ldf
9. Open a command prompt and (provided everything so far has worked) connect to MSDE and attach your timetables using the following (you won't have been able to detach them as you couldn't connect to the corrupt MSDE in the first place):

osql -E

If that has worked, you will now see a prompt as 1> (as opposed to C:>something)

Use the following (note the case and the commas, and lack of comma on line 3):

1> EXEC sp_attach_db @dbname = N'mytimetable',

2> @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mytimetable.mdf',

3> @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mytimetable.ldf'

4> GO

Repeat for any other timetables you have.

Because you gave MSDE the same instance name as before, and you've reattached the databases, you should find that CELCAT springs back into life, with the same permissions etc.  You will be lacking the list of the backups you've made.  This can be resolved by further reading at http://support.microsoft.com/kb/325003

If CELCAT hasn't come back to life for you then you may need to do further investigation.  It's still not clear why it happened.  I had all the data on tape backup so rather than it being a massive stress has just been time consuming to fix.

Hope this helps some of you if you're in a similar situation.

Chris Hodgson, Keele University