MS SQLserver guru..... HELP !!!!

[Kbee]

I need help with sql server 2000. I have a torn page[s] and the stupid thing refuses to start because the DB is marked suspect.
I have tried to issue a sp_resetstatus [after doing the usual sp_configure 'allow updates', 1 ] and then tried to do "DBCC CHECKDB ('Tacoda_Main', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS" but dbcc complains that the DB is "STILL MARKED SUSPECT !!!!"
I thought of using 'alter datase' and turning off torn_page_detection BUT THE DB MUST BE LOADED TO DO THIS!!!
i am at my wits end [did not take long] and this is really pissing me off now this is why i use UNIX ONLY !!!
I realy need to recover this DB [please dont say "do it from backup"], this is a new install, about 1 week old [ok week and .5] , however 400GB of targeting data is in this [of which 80GB is new]. so what i need is a way to recover data, I know there must be a registry option to turn off this torn page detec... so when sqlserver manager starts it does not try to do a torn page check on all the DBs.
-Kurt

[jamrock]

Kbee,

Don't have much experience with torn pages. Will have to do some research.

Questions:

Are you running in full recovery mode? That is do you have a transaction log?

Do you have a test server that we can experiment with?

[jamrock]

Two more questions.

Do you have a backup of all databases just after SQL Server installation?

How have you been bringing the data into SQL Server?

[Kbee]

tape backups exist of the very old database, I do it by using sp_addumpdevice 'tape', 'some_dumb_name','path_to_dev' and then issuing the BACKUP DATABASE statment to the named dev.
I have been creating a stored proc to do this automatically but with the upgrade of the system last week i have put that off.
However before the database upgrade [that is moving from the tacoda 2.1to 2.5 DB] the DB was stopped and the MDFs backed up.
-Kurt
P.s. I know that a backup can be set in the enterprise manager , however I have always prefered the cli and query analyzer is a close to cli as it gets in windows.

[jamrock]

What recovery mode are you using? If you have a transaction log we might be able to do something.

[Kbee]

transaction logs will only give point in time recovery, needless to say they will be very ineffective in restoring 80 GB worth of data

[jamrock]

Okay,

I just had a look at SQL. Here is something you can try.

I would really feel more comfortable if you could do an install of SQL on a machine somewhere and test this.

Whatever you do, you NEED to stop the SQL services and backup the directories where SQL is installed. If all else fails, this should allow you to restore the SQL files from a backup. Remember a test environment is best!!!

Start up Enterprise Manager and drill down to the server and then to the databases. Right click on the database in question and choose properties. Choose options. You can then remove torn page detection from that database.

If your database is damaged, removing torn page detection won't fix it. You may have to dump the data out, create a new database and get the data back in.

[jamrock]

transaction logs will only give point in time recovery, needless to say they will be very ineffective in restoring 80 GB worth of data

Not too sure about that... Restoring 80 GB of data is the same as restoring 1 MB. It just takes longer. My main concern is the integrity of the database.

Option one (my preference) is to do an install SQL on another machine. Restore the last good backup and apply the transaction log to it. This would mean that your data would not be damaged.

I accept your point re: the amount of time involved.

I would be using non-SQL backup and restore (Ntbackup.exe, Veritas etc.) to move the data across to the new server. That way I wouldn't run the risk of changing the status of any files.

Once you are back on your feet, give me a shout if you want to discuss some backup/recovery options. Hope this helps.

[jamrock]

Going to catch some zzzzzzzzzz's. I will check back in the morning.

[Kbee]

Thanks for the advise, I think I am going to try something [somewhat risky] ,but I will tell more tomorrow.
-Kurt