July 8, 2004 at 8:22 am
One of my (SQL 2000) DBs was 'suspect' on my Test environment server.
I attempted to restore (force overwrite) from a previous full backup. The restore failed and then somehow the DB became detached. I attempted to re-attach the DB but now I am getting the following error:
Server: Msg 9004, Level 21, State 10, Line 1
An error occurred while processing the log for database 'mydatabase'.
Connection Broken
I checked that the paths are correct for the sp_attach_db command. The log is of significant size, could that be the problem? I have previous backups, but it would be a lot of trouble to retrieve them.
What are my options if any? Am I doing something wrong or am I missing something?
July 8, 2004 at 10:11 am
The 9004 error message indicates that the log dump is corrupt.
Drop the suspectd database and restart the restoration.
July 8, 2004 at 10:34 am
The databse has been dropped. It is detached. I am attempting to re-attach, but it will not let me because of the corrupted log. I did not detach the log, and I believe that is the reason I also cannot use sp_attach_single_file_db. It keeps looking for the associated log, and then gives me the same 9004 error.
Is there any way to fix the log (ldf file) prior to attaching?
July 8, 2004 at 11:34 am
Try this as an option if your restores are failing:
1. First you must modify master to allow the update of system tables. This is done by executing the statement below within Query Analyzer.
USE master
sp_configure 'allow updates', 1RECONFIGURE WITH OVERRIDE
2. Attempt to reset the databases status by executing the statement below
use master
EXEC sp_resetstatus '<dbname>'
3. Verify that your database’s status has changed from “suspect”. Can you query data from the database?
If in the event your database resets or remains suspect you can place the database into “Emergency Mode”. This mode is typically a last resort but will allow queries, DTS and BCP to be ran. To place a database into emergency mode execute the following:
Create a new database with whatever name you desire. Stop SQL Server and replace the .MDF and .LDF with copies of the suspected files. Rename as needed. Start SQL Server and verfiy the new database is visiable. It will most likely be listed as suspect. Now from Query Analyzer execute the following:
UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
At this point you will be able to extract most if not all data into a new container. Once you have competed the recovery operation you will need to execute the following to remove updates to the system databases.
USE mastersp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
Now you can clean up your work and should have most if not all data recovered. The one thing that will cause all of this to fail is if the file header is corrupt. You could try to restore your backups to another server/instance... I have used this method as a last resort for client systems when there wer no other choices available.
This and 25¢ will get you a cheap cup of coffee.
July 8, 2004 at 11:41 am
1. Create a new database with the same name or a different name. You will have to use a different physical file name, which is fine.
2. Stop SQL Server.
3. Rename the new data file that was created to something else (ex: add.bak to the end)
4. Rename the old data file that you want to restore to the name of the newly created file (the same name as the file you changed in the step above)
5. Start SQL Server
Now the db will still be suspect but you now have a log file.
6. Switch to emergency mode on the database as homeri14 suggested.
7. Stop and restart SQL Server.
8. If database is still in emergency mode, run sp_resetstatus '', restart SQL Server.
July 8, 2004 at 1:44 pm
First, thanx for the help.
I followed Allen's advice and brought the DB back into suspect mode. I then used homeri14's advice and have got the DB into Emergency mode.
Allen suggested then starting and stopping SQL Server which I did, but it is still in emergency mode.
I tried the sp_resetstatus but I get the following message:
Prior to updating sysdatabases entry for database '',
mode = 0 and status = -32768 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status
are already correctly reset. No error and no changes made.
It is still in emergency mode.
Is there any way to get it back online from here?
OR
If I absolutely have to copy the schemas & data off, is there
some sort of recursive script or do you have any suggestions
on how I should do this?
Thanx (again) in advance for your help.
July 8, 2004 at 1:47 pm
Do you restart SQL Server after running sp_resetstatus? Try that.
July 8, 2004 at 1:51 pm
Grasshopper,
Now that you have the database is emergency mode try to execute a query via QA to confirm that you can access the data. If you can then you can use the DTS wizard and choose the query option. DTS your recovered data into a clean database.
This and 25¢ will get you a cheap cup of coffee.
July 8, 2004 at 1:57 pm
I stopped and restarted SQL Server after running the sp_resetstatus. No change - still in emergency mode.
I can see the tables now and have successfully run a query against a few of them.
Is my only hope now to copy the data off to another DB, delete the corrupt DB, and then recreate it and copy the data back?
July 8, 2004 at 2:18 pm
Using the options that I sugestted, that is the only solution that I have found to work. Once you have your data copied to a new DB you will need to look at the normal stuff (indexes, Pk/FK, constraints, etc.) Hope that this gets you what you need.
This and 25¢ will get you a cheap cup of coffee.
July 8, 2004 at 2:27 pm
Restore the database from your backup.
July 8, 2004 at 3:07 pm
Thanx to All.
I really appreciate it.
I was able to copy the tables and data using DTS wizard.
It's the long way around, but, better than losing it all.
THANX!
July 9, 2004 at 4:55 am
Glad that I could help.
This and 25¢ will get you a cheap cup of coffee.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply