Detach versus Offline

13 January,2017 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Detach versus Offline


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer