19

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?

Improve this question

5 Answers 5

Reset to default
18

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

Improve this answer
5
  • 4
    +1, or another drive/folder/SAN...
    – Chris S
    Nov 12, 2010 at 14:51
  • 1
    Is that the only use for "take offline"?
    – Greg B
    Nov 18, 2010 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, 2010 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, 2010 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, 2010 at 16:48
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.

Improve this answer
2
  • 3
    I like the idea of taking offline prior to deleting
    – Greg B
    Nov 25, 2010 at 9: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, 2014 at 2:53
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.

Improve this answer
2
  • 2
    Correct. If you want to be able to do much with the database you're better off putting it in single user mode. Then you still restrict access but can do a lot more.
    – Shane
    Nov 12, 2010 at 15:39
  • You can't even rename it!
    – Dima Fomin
    Jan 13, 2019 at 16:42
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.

Improve this answer
-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 )

Improve this answer
4
  • +1, also older DBMS for defragging or shrinking the DB.
    – Chris S
    Nov 12, 2010 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, 2010 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, 2010 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, 2010 at 7:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .