PDA

View Full Version : SQL wizards?


PosterBoy
25-03-2003, 14:48:41
I've done a very silly thing and need help

After being put in a position where I had to deal with a SQL problem without very little SQL knowledge I have screwed a clients main database up.

I backed up the transaction log and then deleted the log. The database is shown as suspect and won't let me restore.

Am I F&%ked?


Any help in sorting this out will be rewarded with much kudos and thanks


__________________

Venom
25-03-2003, 15:26:27
We can start posting in here PB. Get out of counterpoints way.

Sean
25-03-2003, 15:59:29
What database software are you using?

zmama
25-03-2003, 16:06:25
Try the info here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp

and here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/entrmgr/em_75cy.asp

Sean
25-03-2003, 16:22:13
Originally posted by Sean
What database software are you using?
This wasn’t a 100-0 attempt, it only just occured to me that by SQL he meant Microsoft SQL Server and not Structured Query Language.

Sir Penguin
25-03-2003, 20:05:53
I bet there are wizards if it's MS SQL.

I don't know anything about SQL servers, but I guess the transaction log is the log of requests served? And for security reasons, if it gets modified then the server won't serve the database? Could you reinstall the server software so that it doesn't know that the transaction log has been modified?

SP

Darkstar
25-03-2003, 22:05:12
There's a way to force SQL Server to reset it's suspect flag. I've just forgotten what it was. SQL Server used to have that problem tons, when it's transaction log needed to grow larger then its limit, but noone remembered to up its limit.

You can lose the transaction log, by the way, without damaging the data. What you want to do is do a complete full backup immediately, to preserve your ability to restore the database. After that, you can force SQL Server to get over itself, and stop saying the data is suspect.

I'll see if I still have notes on how to instruct SQL Server to get over it. But don't hold your breath...

Darkstar
25-03-2003, 22:07:55
BTW, if you call in a MS incident, they can give you the commandline you have to type to get SQL Server to reset the suspect block. They are not supposed to/do not document it in MSDN, nor do they list it in SQL Server manuals...

Still digging...

Darkstar
25-03-2003, 22:38:35
Found at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_494j.asp

I guess they got tired of people screaming for that info online.

IIRC, that's a slightly different procedure from what I was told, but I couldn't find my original notes and its been a few years.

When you reset the Suspect flag, that's all it does... turns it off. If you have only trashed out your transaction log, you are ok. Just do a full backup of the poor database, reset/recreate your transaction log, and reset the Suspect flag, and you SHOULD be good to go, as long as everything else is alright.

And I'm glad to see that they finally published that thing.

Venom
28-03-2003, 21:00:06
Just to let you know I managed a restore from the disk. Don't forget, under the options tag, to select a valid location for the restore.

PosterBoy
01-04-2003, 13:34:49
I did it using this

http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html