May 1, 2007 at 7:08 am
Sorry I didn't backtrack far enough in the message.
Ok try this, create a database with the same name and the same MDF and LDF file names on your server. Just a blank database not from the restore.
After doing so stop SQL Server service and copy the original MDF and LDF from wherever you have a copy of the corrupted one and drop it into the folder where the new ones were created and replace them.
Restart SQL Server and see if the database changes states or is now up. If up the run DBCC CHECKDB as stated before however if not what state is the DB in now.
From you message it may be the DB still show itself as open and not closed and I believe there is a convoluted way to get around this but let's see what happens first (and I have to find my notes as to the site I found the info before).
May 1, 2007 at 8:31 am
SQL no longer loads after trying that.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
Additionally, this isn't a plausible solution, even if it did work on my system--I can't simply delete the database on the actual server and replace it with a corrupted one. Like I've mentioned, there's new data being written to it right now, and even if I COULD make an instant backup and restore it, we'd still have serious issues with outright deleting a database in a setting like this.
May 1, 2007 at 8:48 am
And all you did was overwrite the new database (not master) with the old on your local machine? You cannot connect to the server in any way or just to the database in question?
No I won't expect you to overwrite in production, just trying to help you get the DB up so you can get to any missing transactions that were not part of the corruption is all I would possibly acheive.
May 1, 2007 at 9:07 am
Correct, that's all that was done. I can't connect to the server (my system) at all. I can connect to others just fine.
May 1, 2007 at 11:23 am
OK then take a look here.
You may say you have a Log File but treat it as thou you don't have the long from from the original DB and foloow these instructions to see if can open for you.
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
May 1, 2007 at 1:15 pm
We haven't successfully been able to put the database into the emergency state (and we've tried a few times--actually, as I think I mentioned, the guy above me tried only that and gave up when he couldn't do it), but I'll give it another shot, I guess. It can't hurt.
May 1, 2007 at 1:21 pm
Well that's great. I can no longer connect to SQL on my computer. At all.
May 1, 2007 at 1:49 pm
Have a look here - http://msdn2.microsoft.com/en-us/library/ms190737.aspx you should be able to get it started using some combination of the mentioned flags - probably -m or -f and then drop the offending database at which point it should start nicely for you.
- James
--
James Moore
Red Gate Software Ltd
May 3, 2007 at 11:06 am
Check the Event Viewer logs as well to see what is tripping the engine. Never had a database cause this issue. If nothing else stop SQL Server rename the MDF and LDF of the corrupt DB and restart, should bring up as suspect or other state. Odd it would have this effect.
May 4, 2007 at 4:01 pm
That's really unfair of your co-workers to say. Having a single backup overwrite itself is a display of complete incompetence as a DBA.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply