18

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?

Greg B
  • 1,598
  • 5
  • 17
  • 32

5 Answers5

20

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.

squillman
  • 37,883
  • 12
  • 92
  • 146
  • 3
    I like the idea of taking offline prior to deleting – Greg B Nov 25 '10 at 09:18
  • 9
    Ditto. I've four thousand-ish databases in our pre-production environments. It's sometimes hard to know who owns what. Taking it offline and waiting for the squeals is occasionally the only option. – Michael Green Oct 19 '14 at 02:53
18

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

mfinni
  • 36,144
  • 4
  • 53
  • 86
  • 4
    +1, or another drive/folder/SAN... – Chris S Nov 12 '10 at 14:51
  • 1
    Is that the only use for "take offline"? – Greg B Nov 18 '10 at 10:55
  • 7
    No, I can think of other reasons. Imagine that you have multiple databases, for multiple applications. One of those applications is getting an upgrade of the front-end application (web app, client, who knows.) You want to disable access to the database by anyone during the upgrade, but you don't want to take the whole server down because of the other databases. That could be a reason to take it offline. I'm sure your imagination could come up with other reasons too. – mfinni Nov 18 '10 at 14:06
  • My imagination can't I'm afraid, hence the question. I didn't think you could alter a database while offline, so I don't see how taking it offline could assist in the upgrade process – Greg B Nov 19 '10 at 22:51
  • 3
    Greg B - i specifically said upgrading the front-end, not the DB itself. In the scenario I described, the DB is left unchanged. It would be taken offline to ensure that no one is accessing it during the transition. – mfinni Nov 20 '10 at 16:48
8

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.

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
0

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.

George Erhard
  • 814
  • 6
  • 12
-3

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 )

Arenstar
  • 3,602
  • 2
  • 25
  • 34
  • +1, also older DBMS for defragging or shrinking the DB. – Chris S Nov 12 '10 at 14:54
  • 1
    re: "changing or upgrading the schema", from books online "OFFLINE The database is closed, shut down cleanly, and marked offline. The database cannot be modified while it is offline." How can you upgrade the schema in this situation? – Greg B Nov 12 '10 at 14:59
  • Sorry.. i was refering to making changes to an inactive database.. Im a MySQL follower, i assumed an upgrade would take place on a database that is marked offline, so it didnt have any concurrency problems with anything else than the change needed.. Perhaps we can leave this out then.. :D – Arenstar Nov 12 '10 at 15:13
  • 3
    Real databases support real backups, you know. You dont need to take nything but a toy ofline to make a perfect in time consistent backup. A db requiring a lock or some other stupid mechanism is a toy. Really. All major databases have no problem with full backups, consistent, while in heavy use. – TomTom Nov 25 '10 at 07:58