September 12, 2011 at 3:22 pm
I was approached today for a crashed SQL 2000 SP2 server with a single 800G database on it. Log is 20G. The database crashed due to the reason in the subject.
I successfully recovered another backup from the same server, unfortunately it was long long time ago and size is only 3G, but structure is the same.
I wonder how do I recover the rest of the data? Where should I start?
I checked sysdatabases, I don't see the damaged database, not even in suspect mode.
Any clue is greatly appreciated.
September 12, 2011 at 3:29 pm
Run this and LET IT FINISH
DBCC CHECKDB(DbName) WITH NO_INFOMSGS, ALL_ERRORMSGS
Did you try to restore that backup on a test server to see if it works and what data it contains?
Any way you can take a backup of the current <corrupt> db?
Any reason (after last time), that you couldn't setup backups on all servers and that you didn't cehck if the backups were usable (we need to know of your current limits)?
September 12, 2011 at 3:37 pm
Did someone detach or drop the DB? Only reasons it wouldn't be in sysdatabases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2011 at 3:43 pm
Ninja's_RGR'us (9/12/2011)
Run this and LET IT FINISHDBCC CHECKDB(DbName) WITH NO_INFOMSGS, ALL_ERRORMSGS
Did you try to restore that backup on a test server to see if it works and what data it contains?
Any way you can take a backup of the current <corrupt> db?
Any reason (after last time), that you couldn't setup backups on all servers and that you didn't cehck if the backups were usable (we need to know of your current limits)?
the backup is really not a backup, i believe it was created several years ago and when the owner found it's growing too fast then he decided to create another data file and start growing there and reaches 800g now and crashed.
that "backup" is working fine now.
i spoke to the guy who is responsible for it, he said due to the db size too big, no backup have been made in the past but he is considering a bk solution but unfortunately it crashed b4 it is implemented.
due to the fact that moving the damaged db files to another server is not applicable now, i will have to do everything on site.
pls consider a relatively safer suggestion.
thanks lots.
September 12, 2011 at 3:44 pm
GilaMonster (9/12/2011)
Did someone detach or drop the DB? Only reasons it wouldn't be in sysdatabases.
i don't know, the guy did tell me he saw it was marked as suspect
September 12, 2011 at 3:48 pm
Ninja's_RGR'us (9/12/2011)
Run this and LET IT FINISHDBCC CHECKDB(DbName) WITH NO_INFOMSGS, ALL_ERRORMSGS
this is not gonna work becoz the db is not even in sysdatabases
here is the error:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'SELS'. No entry found with that name. Make sure that the name is entered correctly.
September 12, 2011 at 4:23 pm
halifaxdal (9/12/2011)
Ninja's_RGR'us (9/12/2011)
Run this and LET IT FINISHDBCC CHECKDB(DbName) WITH NO_INFOMSGS, ALL_ERRORMSGS
this is not gonna work becoz the db is not even in sysdatabases
here is the error:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'SELS'. No entry found with that name. Make sure that the name is entered correctly.
I got 1 idea left but it's not going to be pretty. I'm hoping Gail has a better option cause mine's going to require a heck of a lot of free space and quite certainly losing data and you won't be able to know what you lost.
What's your SLA, RTO & RPO on this DB? And when were you called in?
September 12, 2011 at 4:42 pm
halifaxdal (9/12/2011)
GilaMonster (9/12/2011)
Did someone detach or drop the DB? Only reasons it wouldn't be in sysdatabases.i don't know, the guy did tell me he saw it was marked as suspect
A suspect database still appears in sysdatabases. If it's not in sysdatabases it's been detached or dropped and there's absolutely nothing that can be done unless you can find the files for that database.
If the DB has been dropped, it's gone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2011 at 5:38 pm
The guy said he saw the DB in suspect but now it's not even in sysdatabases; whether he dropped it or not, I have no idea. I tried to attach it, I got error 5159: Operating system error 2 (The system cannot find the file specified.) on device 'D:\SQL\SELS_Log.LDF' during ForwardLogBlockAHeadAsync. Could not open new database 'SELS'. CREATE DATABASE is aborted.
Maybe I can rename the mdf/ldf, create the DB, then replace the empty mdf/ldf with the crashed one?
September 12, 2011 at 6:31 pm
I renamed the original two damaged mdf/ldf, I created the db with the same name so now I have 2 files that make the db up and running.
I then stopped the db, deleted the two empty files, renamed the damaged files back to the original names.
I tried to bring the db online, failed.
I checked the log file, please see the attachment.
September 13, 2011 at 12:04 am
as mentioned earlier, the database is gone. Look for some backup if they have, if not then they can learn from this disaster.
----------
Ashish
September 13, 2011 at 12:55 am
Run SP_configure and set allow updates to 1. Update sysdatabases for that database and set the status to 32767.
Then see if you can access the DB and run checkdb. Not repair, just with the no_infomsgs and all_errormsgs options.
There's far more wrong here than just a suspect DB. You've got access violations all over the error log. This is one sick server. I'd suggest calling CSS, but SQL 2000 is out of support so they won't help you.
Your also running on a very low service pack of SQL 2000. SQL 8.0.818. Offhand I don't know what service pack that is, but it's not the last one available. I strongly suggest you patch that server to the latest service pack and consider an upgrade to 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2011 at 5:20 am
GilaMonster (9/13/2011)
Run SP_configure and set allow updates to 1. Update sysdatabases for that database and set the status to 32767.Then see if you can access the DB and run checkdb. Not repair, just with the no_infomsgs and all_errormsgs options.
There's far more wrong here than just a suspect DB. You've got access violations all over the error log. This is one sick server. I'd suggest calling CSS, but SQL 2000 is out of support so they won't help you.
Your also running on a very low service pack of SQL 2000. SQL 8.0.818. Offhand I don't know what service pack that is, but it's not the last one available. I strongly suggest you patch that server to the latest service pack and consider an upgrade to 2008.
I certainly will try your advise and for sure I will upgrade it to 08/05 if I will be designated to take over this server/db, for now, I just want to try my best effort to see how much I can get the data back.
818 is SP2, can't believe it, eh? Me too.
September 13, 2011 at 5:39 am
halifaxdal (9/13/2011)
GilaMonster (9/13/2011)
Run SP_configure and set allow updates to 1. Update sysdatabases for that database and set the status to 32767.Then see if you can access the DB and run checkdb. Not repair, just with the no_infomsgs and all_errormsgs options.
There's far more wrong here than just a suspect DB. You've got access violations all over the error log. This is one sick server. I'd suggest calling CSS, but SQL 2000 is out of support so they won't help you.
Your also running on a very low service pack of SQL 2000. SQL 8.0.818. Offhand I don't know what service pack that is, but it's not the last one available. I strongly suggest you patch that server to the latest service pack and consider an upgrade to 2008.
I certainly will try your advise and for sure I will upgrade it to 08/05 if I will be designated to take over this server/db, for now, I just want to try my best effort to see how much I can get the data back.
818 is SP2, can't believe it, eh? Me too.[/quote
It's actually more Around SP3
http://www.aspfaq.com/sql2000builds.asp
You're only 5 years behind on the patches :-D.
Denali's coming out in a few months if you can wait it out :Whistling:
September 13, 2011 at 5:42 am
halifaxdal (9/13/2011)
GilaMonster (9/13/2011)
Run SP_configure and set allow updates to 1. Update sysdatabases for that database and set the status to 32767.Then see if you can access the DB and run checkdb. Not repair, just with the no_infomsgs and all_errormsgs options.
There's far more wrong here than just a suspect DB. You've got access violations all over the error log. This is one sick server. I'd suggest calling CSS, but SQL 2000 is out of support so they won't help you.
Your also running on a very low service pack of SQL 2000. SQL 8.0.818. Offhand I don't know what service pack that is, but it's not the last one available. I strongly suggest you patch that server to the latest service pack and consider an upgrade to 2008.
I certainly will try your advise and for sure I will upgrade it to 08/05 if I will be designated to take over this server/db, for now, I just want to try my best effort to see how much I can get the data back.
818 is SP2, can't believe it, eh? Me too.
p.s. That should not be done on the production server...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply