March 3, 2023 at 5:33 am
Consistency error in DB. 1 page from sys.syscolpars table is missing. There is no any impact but the thing is can't able to upgrade the DB from 2008 to higher version. Backup restore fails. Old good backups are not available.
As this is a sys schema table so needed DAC connection to query it. But even through DAC connection, not able to do 'DBCC repair rebuild' or Drop-index-rebuild index, as its not allowing ay DML activity.
Tried to take a backup into new table with select * into (hoping i will rename & keep new table and drop original one) but can't do this. Sys schema perhaps, not allowing to create new table.
I tried to script out the entire DB (as its 3gb only) to create a new db from it, but application sprocs are encrypted. I can decrypt sprocs individually using some free utilities available online ( haven't tried yet), but thats not I want basically. I want the whole script in a single, composite way, at one place.
What is the way forward? Please help.
March 3, 2023 at 1:30 pm
Probably your best bet is to export the database to flat files. You could try reading from the tables into another database too. Otherwise, you may be down to trying DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS.
This is why it's so very important to test databases and test backups, because once you get here, where you literally can't run a backup, and you don't have backups available, you're in a world of hurt.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 13, 2023 at 4:27 pm
I never heard this sys table is missing error and no good old backups together.
I suggest the same Grant said, try the data export to another newly created DB (which is good for backup and restore) or if DB is small use flat files.
keep the mdf and ldf backed up and try checkDB datalosss.
I hope if SQL restarts the DB comes online.
Regards
Durai Nagarajan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply