I just saw this on one of the forums – it’s another wonderful piece of advice to studiously avoid.
Scenario: Drive goes bad and causes a torn page error. User wants to fix the problems and move the database to a different drive.
The advice given by one person was just to detach the database with the torn-page error, move it to the new drive and re-attach it.
Luckily there were a few other sensible people around (I was on a plane flying back from TechEd) to interject some real advice, including:
- using the backups rather than copying files about
- or, if no backup exists, moving it by stopping SQL Server, copying the files off the bad drive, swapping out the bad drive, putting in the new drive with the same drive letter as the damaged ones, copying the files back into exactly the same directory path as before and restarting SQL Server. Also, taking a backup of the damaged database before doing all of this.
So why was detach/attach bad advice? The problem comes with the process of doing the attach. It’s quite possible that the damaged page(s) are required during attach, and if they’re damaged then attach will fail and there’s nothing you can do to force it to work.
Bottom line: don’t ever detach a damaged database if you don’t have valid backups to restore from.