sql 2008 - three databases cannot be backed up - message '170(failed to retrieve text for this error. Reason: 15105)'

Question

sql 2008 - three databases cannot be backed up - message '170(failed to retrieve text for this error. Reason: 15105)'

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

GTR (3/13/2010)
Yes, that is why, what if mdf and ldf files are corrupt while attaching detaching databases? That is why we have best practices am i wrong?

You need to read This SSC Article[/url] by Jonathan Kehayias.

Both methods will work though.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

GTR SSCrazy Eights Points: 8007 More actions · Answer 2

Paul White (3/13/2010)
GTR (3/13/2010)
Yes, that is why, what if mdf and ldf files are corrupt while attaching detaching databases? That is why we have best practices am i wrong?
You need to read This SSC Article[/url] by Jonathan Kehayias.
Both methods will work though.

Yes, you are correct both methods will work, but i have seen issues like files corrupt while attaching\detaching method. First solution Microsoft recommends is use backup\restore method for migration.

EnjoY!

Paul White SSC Guru Points: 150468 More actions · Answer 3

GTR (3/13/2010)
Yes, you are correct both methods will work, but i have seen issues like files corrupt while attaching\detaching method.

And I have seen backup/restore fail too. Either will work, neither is inherently 'better'.

GTR (3/13/2010)
First solution Microsoft recommends is use backup\restore method for migration

You must stop posting such absolute statements without quoting a reference.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

GTR SSCrazy Eights Points: 8007 More actions · Answer 4

Paul White (3/13/2010)
GTR (3/13/2010)
Yes, you are correct both methods will work, but i have seen issues like files corrupt while attaching\detaching method.
And I have seen backup/restore fail too. Either will work, neither is inherently 'better'.
GTR (3/13/2010)
First solution Microsoft recommends is use backup\restore method for migration
You must stop posting such absolute statements without quoting a reference.

We talked with microsoft engineer before migration, he recommended Backup\Restore.

Here is the reference,

http://support.microsoft.com/kb/314546

EnjoY!

Paul White SSC Guru Points: 150468 More actions · Answer 5

GTR (3/13/2010)
Here is the reference http://support.microsoft.com/kb/314546

From the KB article:

To move user databases, use one of the following three methods.

That article describes three methods, backup/restore, attach/detatch, and Import/Export Data Wizard.

It expresses no preference for any method.

Care to try again?

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

GTR SSCrazy Eights Points: 8007 More actions · Answer 6

Paul White (3/13/2010)
GTR (3/13/2010)
Here is the reference http://support.microsoft.com/kb/314546
From the KB article:
To move user databases, use one of the following three methods.
That article describes three methods, backup/restore, attach/detatch, and Import/Export Data Wizard.
It expresses no preference for any method.
Care to try again?
Paul

Yes, I think when someone writing an article they tend to write preferred method first, then the alternate method, correct me if i am wrong.

Here is another article why backup\restore is preffered over attach\detach.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Moving-databases-around-what-are-your-options-and-across-what-versions.aspx

EnjoY!

Paul White SSC Guru Points: 150468 More actions · Answer 7

GTR
Yes, I think when someone writing an article they tend to write preferred method first, then the alternate method, correct me if i am wrong.

Consider yourself corrected, then. Many times, inferior methods are presented first, followed by the coup de grâce...the preferred method.

You are not seriously relying on the 'tendency' to list one method first as the basis for your assertion that backup/restore is a Best Practice, are you?

Here is another article why backup\restore is preffered over attach\detach.

Nope. Kimberly lists 'pros and cons' for both methods. Her personal preference, specifically when upgrading a database from one version to SQL Server to another does not make it a Best Practice. Both are perfectly valid, as I keep saying.

I would remind you of your original statement:

Attach and Detach is not recommended for migration.

Really? :laugh:

We can go over this for as long as you like. Just because you are convinced that backup/restore is superior does not make it so.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

GTR SSCrazy Eights Points: 8007 More actions · Answer 8

I would remind you of your original statement:
Attach and Detach is not recommended for migration.
Really? :laugh:

I meant Backup\Restore is preferred over Attach and Detach, sorry for the ambiguity.

We can go over this for as long as you like. Just because you are convinced that backup/restore is superior does not make it so.

It is your call:cool:

I don't know Paul how many references you want. I think the one with more Pros is considered as preferred method over the other method which has more cons.:hehe:

EnjoY!

Paul White SSC Guru Points: 150468 More actions · Answer 9

GTR (3/14/2010)
I meant Backup\Restore is preferred over Attach and Detach, sorry for the ambiguity.

At last! Do try to be more clear in future, it saves the sort of discussion we have just had.

GTR (3/14/2010)
I don't know Paul how many references you want.

Just one 🙂

What I asked for was a reference to support your original statement.

Now that you have changed your statement, it is no longer required.

Good job really, since it does not exist 😛

GTR (3/14/2010)
I think the one with more Pros is considered as preferred method over the other method which has more cons.

You are welcome to make your own assessment to form a personal opinion - of course.

Counting pros versus cons may not be an entirely satisfactory methodology for everyone 😀

Some people might be tempted to consider the weight of each consideration as it applies to their situation...

To make a serious point for a second. If your only reason to prefer backup/restore over attach/detach is because of a very, very small risk of corruption as the detach takes place, I would suggest you think some more. Kimberly's point that there is no way to revert to the previous version with attach/detach has the same obvious logical flaw: if the migration fails for whatever reason, we can always restore from backup :laugh:

So, to summarize:

GT's personal preference is for Backup/Restore. :rolleyes:

I suggest carefully considering the relative merits of both options, as they apply to a specific situation.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

GTR SSCrazy Eights Points: 8007 More actions · Answer 10

Paul White (3/14/2010)
GTR (3/14/2010)
I meant Backup\Restore is preferred over Attach and Detach, sorry for the ambiguity.
At last! Do try to be more clear in future, it saves the sort of discussion we have just had.
GTR (3/14/2010)
I don't know Paul how many references you want.
Just one 🙂
What I asked for was a reference to support your original statement.
Now that you have changed your statement, it is no longer required.
Good job really, since it does not exist 😛
GTR (3/14/2010)
I think the one with more Pros is considered as preferred method over the other method which has more cons.
You are welcome to make your own assessment to form a personal opinion - of course.
Counting pros versus cons may not be an entirely satisfactory methodology for everyone 😀
Some people might be tempted to consider the weight of each consideration as it applies to their situation...
To make a serious point for a second. If your only reason to prefer backup/restore over attach/detach is because of a very, very small risk of corruption as the detach takes place, I would suggest you think some more. Kimberly's point that there is no way to revert to the previous version with attach/detach has the same obvious logical flaw: if the migration fails for whatever reason, we can always restore from backup :laugh:
So, to summarize:
GT's personal preference is for Backup/Restore. :rolleyes:
I suggest carefully considering the relative merits of both options, as they apply to a specific situation.
Paul

Hahahahahahaha!!!!!:-P

EnjoY!

mathias-ruehn SSC-Addicted Points: 489 More actions · Answer 11

Hi,

sorry that i did answer on weekend,

needed a few hours free time... :-).

i tried to backup / restore vor migration,

but got the same message

(failed to retrieve text)

as i tried to restore the databases

in the newly created ones.

database engineer said,

that this error occurs if there are not the same pathes

in the restoring database???

so we did the detach / attach method.

maybe they were corrupt before migration?

(there are other databases who have no problem in backup / restore)

So, actually there is only one dc,

running 2008 r2 in 2008 r2 mode.

(2003 removed)

i checked all network binding again,

and so on.

No, some backup jobs runs fine, seconds later, the same error,

then again successfully...

i´m taking a shot... 🙂

so,

what do you think?

are there io problems or are there problems with the databases?

is there a mehod where i can check if databases are OK?

dbcc checkdb no problem, check indexes, no problem.

second thing is, that same hardware, same config, same sql, etc,

is running in same environment for usa in the same datacenter...

mathias-ruehn SSC-Addicted Points: 489 More actions · Answer 12

auto close is by default disabled,

actually shure not enabled and had never been enabled...

GTR SSCrazy Eights Points: 8007 More actions · Answer 13

Can you run this query now and post the result here?

SELECT name, state_desc from sys.databases

EnjoY!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 14

mathias-ruehn (3/15/2010)
i tried to backup / restore vor migration,
but got the same message

Backup/restore, detach/attach will have the same result, a database in SQL 2008 format.

database engineer said,
that this error occurs if there are not the same pathes
in the restoring database???

Nope. If the paths are wrong, you'll get an error trying to restore, an error that you will have to rectify before you can restore the database

are there io problems or are there problems with the databases?

There is some form of IO problem. The error that you are finding in your logs is error 825, it means that there is intermittent IO failures. Either the OS is not returning a page when asked or it is returning it corrupt.

I can't tell you more than that, the error isn't that specific. It could be anything in the IO path (antivirus, faulty drivers, bad cache, fibre switch, fivbre cable, SAN controller, disks, etc)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

S. Kusen SSChampion Points: 10936 More actions · Answer 15

I notice that you are trying to backup to the same disk you're data files live on. I agree with Gail that this is an IO issue based on the 825 errors. 823, 824, and 825 suggest IO problems. Per this article from Paul Randal, he explains that an 825 error is a sign of "impending doom":

http://www.sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx

The error Gail refers to is on the MDF file (I didn't dig through the log further to see if the other two databases are also referenced, and/or what files were affected), so I don't think trying to backup to even another network path will be beneficial because the read error is occurring on the MDF file itself.

It could just be that those three databases are sitting on a bad part of the disk, and that is why those three consistently fail.

I would recommend trying to get another LUN presented from a different storage array and hopefully *copying* the MDF/LDF to the new LUN. Taking SQL Server offline and backing up the MDF/LDF files to a tape or different disk would be an additional fail-safe. Then, I would detach the database, and attach the MDF/LDF that was copied to the new LUN and then try the backup.

From a best practices standpoint, I would also try to, at a minimum, separate data, log, and backup files to different LUN's on different physical spindles in your array. We use six LUN's at my company: 1) system databases, 2) tempdb data files, 3)tempdb log file, 4) user DB files, 5) user DB log files, 6) all DB backups.

Steve