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.
For moving files between two different servers or on the same server? How/why would you use backup/restore to move the database files from say D to E on the same server which is what this article was about? I have a different article called Use Backup/Restore to Minimize Upgrade Downtimes
that would apply to moving the database to a different server.
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.
I am sorry, but you are incorrect here. When you go from OFFLINE to ONLINE the database does go through recovery again. You can actually see this by monitoring the database state_desc column in a loop while setting the database to ONLINE. Create a database on a dev server named test and set it to OFFLINE. Then run the below code in a query window and then bring the database ONLINE again.
create table #db_state_changes
(state_desc sysname, collecttime datetime default(getdate()))
insert into #db_state_changes (state_desc)
where name = 'test'
waitfor delay '00:00:00.005'
Once the database is ONLINE stop the execution of the above and then do:
select * from #db_state_changes
If you scroll through the results you should come to something like:
OFFLINE 2010-09-24 13:34:27.640
OFFLINE 2010-09-24 13:34:27.647
ONLINE 2010-09-24 13:34:27.650
RECOVERING 2010-09-24 13:34:27.677
RECOVERING 2010-09-24 13:34:27.683
RECOVERING 2010-09-24 13:34:27.690
RECOVERING 2010-09-24 13:34:27.693
RECOVERING 2010-09-24 13:34:27.700
ONLINE 2010-09-24 13:34:27.707
ONLINE 2010-09-24 13:34:27.713
ONLINE 2010-09-24 13:34:27.720
If the log file wasn't available and the database was in full recovery, the database would not go online, it would go into Recovery_Pending like this:
OFFLINE 2010-09-24 13:42:58.457
OFFLINE 2010-09-24 13:42:58.460
ONLINE 2010-09-24 13:42:58.467
RECOVERING 2010-09-24 13:42:58.493
RECOVERING 2010-09-24 13:42:58.500
RECOVERY_PENDING 2010-09-24 13:42:58.507
RECOVERY_PENDING 2010-09-24 13:42:58.510
RECOVERY_PENDING 2010-09-24 13:42:58.517
RECOVERY_PENDING 2010-09-24 13:42:58.523
The only exception I have found to this is for SIMPLE Recovery Model databases that shutdown cleanly where it creates a new log file, but that is like playing Russian Roulette at best and not something you'd want to gamble with.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008 My Blog
| MVP ProfileTraining
| Become a SQLskills InsiderTroubleshooting SQL Server: A Guide for Accidental DBAs