Home / server / Questions / 201171
Accepted
Greg B
Asked: 2010-11-13 06:40:38 +0800 CST

Why would I take a database offline?

  • 772

For what reasons and in what situations would you use the "Take Offline" / ALTER DATABASE dbName SET OFFLINE feature.

What tasks can you perform with an offline database? What tasks can you only perform with an offline database?

sql-server

5 Answers

  1. In development / staging environments it's sometimes useful to take a database offline to make sure that you're connecting to the right instance of the database in the application and that you don't have a jacked up connection string somewhere.

    That said, in this situation it's a much better idea to have different database names for the different environments and a build process that will automatically configure your connection strings...

    Similarly, I like to take databases offline for a period of time prior to decommissioning them in the (not-so)off-chance that for some reason they need to come back online. I've been bitten quite a few times by devs who have hooks that I don't know of into one of my databases when I want to get rid of it. Taking it offline much less drastic and less time consuming than deleting it and restoring it if needed.

    Another thing would be as an emergency safeguard. I've had to do this before. Sometimes a nasty bug is found in your app that, even though not malicious, will still corrupt data in your database. Taking the database offline is a quick way to stop the bleeding until you get the bug identified. You can then bring it back online to assess the damage within the database.

    • 20
  2. Best Answer

    You can move the MDF file to another server to put it online.

    • 18

  3. While some of the answers here may give you some ideas regarding what you can do with a database that has restricted access, not much can actually be done with a database that is offline. You can't update, upgrade, add or delete data, etc.

    • 8

  4. My overarching reason, the one I have to sell to the DBA's more often than I'd like to enumerate...

    "The SAN needs repair... and no I can't just hot-swap a drive, the backplane/controllers are at fault."

    DB instances behave very badly when their disks go away.

    Thus, I shut the SQL instances down prior to taking the SAN offline, and then bring them back one at a time so that they don't contest resources - first instance up takes all cluster resources and becomes the Active DB node, subsequent instances run as Passive nodes.

    • 0

  5. There are many reasons why you would need to do this..

    For an example, 

    consider changing or upgrading the actual database program/binary...
consider changing or upgrading the schema or tables..
consider changing or upgrading index's..

    The most important point..

    Is taking a backup.. to get a perfect snapshot in time..

    ( in some DB's you just create a lock on all tables )

    • -3