question

iamkudam avatar image
iamkudam asked

Is there any otherway to repair my DB?

Hi Fellow DBA's

I have a rather sad situation here and i hope i can get some assistance here on this forum. I have a DB that has been failing to back up since September, we only realised this late in December because we were sloppy.

When i run a backup here is the error that i get, all the time. It runs up to 50% all the time. I am dreading to restore a September backup because the information there is old, and it means that it might take a month of recapturing again.

1. I have run a DBCC CHECKDB set to single user and here is what i get.

2. Seems there are 0 errors and 0 inconsistency on the database. Yet it does not backup. The results are as follows:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'People'.

My question is where does this error come from and how best can i fix it, without restoring a September backup. PS: We are still able to use this DB and write information on it, we just cant back it up - GUI or T-SQL route. Is there a way to copy the DB in its current state or clone it?

Running SQL: Server 2012

Thank you for your assistance in advance.

databasedbcccorrupt_backup
err-sql2.png (469.2 KiB)
dbcc-result-1.jpeg (35.5 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Maybe DBCC CHECKDB reports no no errors because it terminates with the error 8921. Was tempdb out of space?

In terms of getting a copy of the DB, then given that you can access it normally, is a good place to be! You could script out the entire database, including the data and then recreate the database under a new name on the same server (or probably better as it sounds like there might be some underlying hardware issues) a different server. You can use SSMS to create the scripts (all objects, security permissions and data), and/or bcp the data out; or even a 'compare' toolset like Redgate SQL Compare and SQL Data Compare. The size of the database becomes a factor here - scripting out GBs of data may take a while, and of course you'd want no one accessing the database and changing anything whilst you did this capture.

With a 'compare' toolset, you could even start with a restore of that September backup - at least then you already have some things in place.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iamkudam avatar image
iamkudam answered

Thank you for your response @Kev Riley i was almost losing hope. The tempdb was on 50%, it was allocated 8MB and i increased that to 3072MB. Further to being able to access the DB we ar able to work normally on it without any issues, the only issue is backing up this DB. My plan is to move the DB to a different HDD once i manage to recover it.

My database is just a little over 945MB.

I have one challenge though - I am a novice when it comes to DB's. How can i create the scripts so i can get the date out? And rather how can i also BCP the data out.

Your assistance is highly appreciated.

Thanks.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iamkudam avatar image
iamkudam answered

Hi @Kev Riley , ok so this is what i did. I created a New Database and i imported my September database to that new database. I downloaded Redgate and ran a data base compare.

Here are the results below.

So should i say deploy, does this mean that my Databases People and PeopleNew will be synced? Your input is highly appreciated.

Thanks

K


redgate.png (148.1 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, PeopleNew will be made to look like People

0 Likes 0 ·
iamkudam avatar image
iamkudam answered

Hi @Kev Riley , i am managed to use Redgate to do a SQL Compare, seems like it worked. However when i check People now (1GB) and PeopleNew now (800MB) there is a size discrepancy that i worry about, would this mean that maybe the data/information its self was not copied across? Please see the below screenshots.

How can i make the 2 databases have the same size. I know this really sounds dumb - i am trying to learn a little, please bear with me :)

Thanks for your help again.


peoplenew-size.png (48.9 KiB)
people.png (104.8 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The size value in that properties page is the total 'on disk' size - so if your data files or log files are different then that could be the difference.

In truth I don't think you'll ever get the sizes to match.

To check that you've got all the data, I would look to check the size of each table in both databases. You still may get slight differences due to indexes being more or less fragmented, more statistics objects. Also consider any other data storage, like full text indexes.

0 Likes 0 ·
iamkudam avatar image
iamkudam answered

Hi @Kev Riley , phew still at it with this database. I seem not to be wining, i have sent it through to a service provider who inturn say the database might be corrupt. I did as you suggested on your first response "You could script out the entire database". After scripting it out what is the next step?

How then do i recover this DB?

Regards,


db-scripted-out.png (151.8 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you have scripted it out, then you simply need to execute the script on a new server and the database will be recreated.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.