Hi Jonathan,

Another benefit of the backup/restore method over attach/detach or offline/online could be that the backup does not flush the plan cache for the database considered.

And I believe there's another subtle difference between setting the database to OFFLINE and detaching it. When you reattach the database, it goes into the recovery process, whereas when the database goes back from OFFLINE to ONLINE, it does not. OFFLINE is a persisted state, and you can verify this by going directly from OFFLINE to EMERGENCY, and thus bypassing the recovery. I have been bitten by some interesting cases of transaction log corruptions not discovered yet because the recovery is not run when the database comes back ONLINE.

Chances that recovery is needed are very unlikely to happen though because you would need either to have exclusive access to the database when taking it OFFLINE or use the WITH ROLLBACK option, which would clean opened transactions before allowing the database to change its state. But as the files are unlocked, they remain unprotected and the tlog could be scribbled by anything, even in user mode.

Another funny thing about the OFFLINE state is that you can drop an offline database, it will do exactly the same as detach: remove the entries from the system tables, and leave the files unlocked on the FS.

Thanks for the article,