Options for Recovering Dead SQL Server?

[mjjensen]

Hey All,

We have been running SQL Server 2000 on Windows XP for some time without a hitch. After leaving a query run overnight, I came back this morning to find that MSSQLServer service manager had stopped the service from running (although the computer had not been touched in that time). I tried to restart the service (Start/Continue) but it will not resume.

I cannot even start Enterprise manager anymore, and it often reports that the SQL Server does not exist or access denied. The problem is I don't know what settings I should be looking for to diagnose the problem, and I don't want to lose any data.

We have a number of NDF data files containing tables and indexes that we obviously want to keep.

The question then arises to the best method for retaining the data. Are these viable strategies:

1. Fixing SQL Server so that the SQL service starts.
2. Transfering the NDF files into another copy of SQL server somehow.
3. Installing another copy of SQL server on the local machine and use the existing NDF files?

Please bear in mind that I have only a few months experience using SQL Server, so any help would be greatly appreciated.

Regards,

Michael Jensen.

 

[tlbroadbent]

Which edition of SQL Server 2000 are you running on Windows XP?

Did you mean MDF files when you mentioned NDF? MDF is the default extension for primary SQL Server database files. LDF is the default extension for Transaction Log files. NDF is the default extension for secondary database files.

You should be able to run SQL server since was running previously. Have you examined the SQL error log for messages? I'm not familiar with Windows XP. Does it have a system event log? If it does, have you examined it for error messages?

You can copy the database and log files to another server and attach them to SQL Server. See the following articles.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071

http://www.sql-server-performance.com/dd_creating_logins.asp

You should also be abble to re-install SQL server on the PC. Setup doesn't touch user files. However it will replace the master, msdn, model and sample databases. Make sure all databases are backed up. You can install SQL Server, stop the service, copy the system database files from backup, replacing the newly created files and then restart SQL Server. All your existing user databases should be attached.

I recommend reviewing pertinent articles referenced in the knowledgebase article "INF: Disaster Recovery Articles for Microsoft SQL Server" at

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q307775

I also recommend making sure there is sufficient disk space available on teh system drive and the SQL data drive. If necessary delete unneeded files. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.