March 3, 2009 at 5:02 am
Thanks, Gail, I was hoping you'd pick this up. Your reply is interesting - does it mean that detaching does something significant to the data files that makes them fit(ter) for copying and attaching, which doesn't happen during a normal close and shutdown? What's the "potential problem"?
March 3, 2009 at 5:19 am
A detach commits (or rolls back) all transactions, writes all dirty data pages to disk, writes a mark in the log indicating a clean shutdown and then it removes the database from SQL.
A shutdown may or may not commit or rollback all transactions, as services are only given so much time to shut down (imagine a transaction that will take an hour to roll back) and it may or may not write dirty pages to disk. If it doesn't then when SQL comes up it has to recover the database. No problems there, that's normal and it's called restart-recovery. If the database files are taken in that state and attached elsewhere, there's a chance that the DB will not attach (especially if you try to attach without the log)
That's why it's said to only attach files that have been explicitly detached. Files from a shutdown instance should attach, but there are cases where they won't.
A detached database is however as intact as a backup and will be attachable elsewhere.
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
March 3, 2009 at 5:55 am
Brilliantly clear. Thank you.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply