October 26, 2011 at 4:30 am
Olá,
i have a DB with about 350 GB like this:
F:\....\xxxxxxx.mdf
f:\....\xxxxxxx1.ndf
F:\.....\xxxxxxx2.ndf
.....
F:\.....\xxxxxxx9.ndf
....
F:\.....\xxxxxxx15.ndf
I had to put the DB on a USB external disk (F:\).
Now, I have a new RAID volume (V:\) and want to put there this DB.
With several attempts always gives error copying the file xxxxx9.ndf. Only this file!
Now I have the DB running on the new volume, but this file is still in the USB disk. The DB is running!
I need to retrieve the data from the file ... it is possible to know which data are available?
The error appears even when I try to replicate the DB in SQL Management Studio ...
What I do not understand is how the DB still works if a file is corrupt!
I am copying tables to another set of DB, we'll see if I can recover a lot!
Question: it is possible to know what data exists in a file xxxxx.ndf ?
P.S.: I have to say that there are no backups...ups! higher orders....
Thank you,
JG
October 26, 2011 at 8:18 am
jorge_gomes98 (10/26/2011)
I need to retrieve the data from the file ... it is possible to know which data are available?
Is that file it's own filegroup or in a filegroup with other files?
The error appears even when I try to replicate the DB in SQL Management Studio ...
What I do not understand is how the DB still works if a file is corrupt!
Possibly because SQL hasn't hit the damaged portion yet. Or maybe it has and you haven't noticed. Any severity 24 errors in the error log.
P.S.: I have to say that there are no backups...ups! higher orders....
Why oh why oh why not? You're likely in for a world of hurt here without a backup and possibly significant data loss.
Run a checkDB, see how bad it is.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
October 26, 2011 at 8:20 am
Any errors in the windows event log? If so, post them.
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
October 26, 2011 at 9:05 am
Q1:
the file is in the same filegroup...."Primary"....grrrrrrrrrrrrrrrrrrrr
Q2:
logs....i have lots of messages in windows event log (application and system) and SQL event log.
see attachments....
obrigado,
JG
October 26, 2011 at 9:25 am
jorge_gomes98 (10/26/2011)
Q1:the file is in the same filegroup...."Primary"....grrrrrrrrrrrrrrrrrrrrp
Then that file contains pieces of all tables in your database, including the system tables.
Q2:
logs....i have lots of messages in windows event log (application and system) and SQL event log.
Yup. World of hurt.
Looks like at the moment the error is mostly intermittent (error 825). It won't stay that way.
Take a backup NOW. Backup to disk, copy the backup to another server (test/dev), restore and run CheckDB. Immediately. Post all results
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
I would suggest that you put that DB into restricted user mode and stop any apps that use it. It's not going to get better with users hitting it. Until you know if you have a clean backup, do NOTHING.
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
October 26, 2011 at 10:16 am
Olá,
I tried backing up the DB ... mistake!
I tried to back up files from DB .... mistake!
I tried to shrink the F:\...\xxxxx9.ndf for other files (V:\....\ ...).... mistake!
I am able to manually copy the tables to another DB, about 20 at a time. When I get an error I have to move to other tables ...
I will lose data, it is true .... well .... bad luck!!
If there are no backups of the initial DB is not my fault! ...i just followed orders.
It was the best solution I found!
P.S. I can not "turn off" the DB, I have applications that need it!
Obrigado,
JG
October 26, 2011 at 10:18 am
I do that myself too often but skipping over those words is EXTREMELLY unwise.
I would suggest that you put that DB into restricted user mode and stop any apps that use it. It's not going to get better with users hitting it. Until you know if you have a clean backup, do NOTHING.
October 26, 2011 at 10:22 am
thank you guys!
I have to go though, they are 17:20 (Portugal).
I have two girls waiting for me and still 40 minutes away by train!
Thank you!
JG
October 26, 2011 at 10:24 am
jorge_gomes98 (10/26/2011)
I tried backing up the DB ... mistake!
What do you mean 'mistake'?
If there are no backups of the initial DB is not my fault! ...i just followed orders.
Are the people who gave those 'orders' going to accept responsibility for the loss of this database? Did they understand the consequences of not having backups?
P.S. I can not "turn off" the DB, I have applications that need it!
You need to declare an emergency and take things down. Right now you have intermittent failures and a degrading drive. If the apps keep hitting it then anything they do can be lost and it could make the DB fail faster.
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
October 27, 2011 at 2:21 am
"
What do you mean 'mistake'?
"
Error! CRC Error!
------
"
Are the people who gave those 'orders' going to accept responsibility for the loss of this database? Did they understand the consequences of not having backups?
"
yes. they understand! it was their choise. I have a document with this order.
------
"
You need to declare an emergency and take things down. Right now you have intermittent failures and a degrading drive. If the apps keep hitting it then anything they do can be lost and it could make the DB fail faster.
"
i understand!
I am now copying tables to another DB. Fortunately, almost all critical tables are replicated.
I try to copy the remaining 20% today ... we'll see if error appears!
This DB is just history, it has historical tables daily. But for now are not used, with some exceptions.
Thank you,
JG
October 27, 2011 at 4:32 am
jorge_gomes98 (10/27/2011)
"What do you mean 'mistake'?
"
Error! CRC Error!
That's not very helpful. What is the EXACT error message you get when taking a database backup?
I am now copying tables to another DB. Fortunately, almost all critical tables are replicated.
I try to copy the remaining 20% today ... we'll see if error appears!
So do you want help recovering this or not?
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
October 28, 2011 at 5:13 am
Hello,
I tried to backup the Db HISTORICO but the error continues.
See attached image and logs from SQL.
I've recover almost all the tables ... some have failed but it is not critical!
So, I wonder if we can recover the data but now only for knowledge.
I already have a new DB Historico_novo with the necessary data.
I am thinking remove disk and with some tool do a scandisk...or chkdsk /f...or...i don't know, i must think.
Just for knowledge.
Thank you all, good weekend!
JG
October 28, 2011 at 5:41 am
I doubt you'll recover anything that you haven't already got.
Probably a good time to impress on management that a good backup would have saved days of work and prevented the loss of that data
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
October 28, 2011 at 5:43 am
I've never bothered with repairing a disk. A new 1 is so cheap these days. Not worth my time to risk losing another db.
I've also always had this in mind. If this file is so UN-important that I don't mind losing it at all, why do I bother backing it up?
If it's so important that I can't lose it? Why take a chance on a HD that has failed before?
Shop around, we have specials in my small town where a 2.5 TB usb drive is under 100$.
October 28, 2011 at 5:59 am
ok, i agree with all!
"
Probably a good time to impress on management that a good backup would have saved days of work and prevented the loss of that data
"
YES! My boss saw that it took me almost a week to recover data ... stopped doing other things and I dedicated myself to this. Time and money away!
Once again, i warned him of the problem of not having backups of this DB ... I've done my job, and well done, I recovered almost everything!
Now they tell me, "Congratulations, you did."
We will see next time!
Thank you. I notice if i can recover bad sectors in failed disk.
JG
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply