Detach versus Offline

Deciding on whether to DETACH a sql server database or take OFFLINE has a similar impact of making the database inaccessible to the end user.

There are some differences. The key difference is that OFFLINE maintains the meta data in the SQL Server. So if you do a select * from sys.sysdatabases the database will still have a record

If you detach , the metadata no longer exists in the database server.

There could be adhoc\emergency reasons why you would want to make the database inaccessible. But deciding on which method to use will depend on a number of factors:

1. Will monitoring report an alert if a database in in OFFLINE mode?
2. If the databases are detached, are the detached files being managed? It is easy for a file to become disassociated from the SQL Server Instance, and to lose the files.
3. If the database archived before it’s detached.
4. Is the database archived before it is made OFFLINE?

Some thought is required around how to recover an OFFLINE or detached database if the db becomes corrupt or lost

Another factor is security. When a database is attached to a SQL Server and the SQL Server is live accessing the file requires SQL Server level authentication. If the database is detached , then the level of access is different. Someone may copy the file, attach to another SQL Server and access the data.

Read More

SQL Server Backup – Restore vs Detach – Attach : which method is faster?

Database backup does not cause blocking (SQL Server DBA)

OFFLINE Database files not deleted when DROP DATABASE is executed