SQL SERVER – T-SQL Script to Take Database Offline – Take Database Online

Blog reader Joyesh Mitra recently left a comment to one of my very old posts about SQL SERVER – 2005 Take Off Line or Detach Database, which I have written focusing on taking the database offline. However, I did not include how to bring the offline database to online in that post. The reason I did not write it was that I was thinking it was a very simple script that almost everyone knows. However, it seems to me that there is something I found advanced and that is simple for other people sometime, in this case, I thought simple and is not simple for someone. We all have different expertise and we all try to learn new things, so I do not see any reason as to not write about the script to take the database online.

-- Create Test DB
CREATE DATABASE [myDB]
GO
-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK
IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO
-- Clean up
DROP DATABASE [myDB]
GO

Joyesh let me know if this answers your question.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

18 thoughts on “SQL SERVER – T-SQL Script to Take Database Offline – Take Database Online

  1. HI Pinal,
    Using GUI I made one of the existing databases as Offline and when i am trying to bring Online either using GUI or SQL getting following error.

    Msg 942, Level 14, State 4, Line 1
    Database ‘TestDB’ cannot be opened because it is offline.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.
    TIA.

  2. Hello Pinal,
    I am new to SSIS and would like to if you have experience in this area. I am looking for the correct steps in creating dynamic connection in SSIS to gather waits stats on all servers (40 plus servers). When I try to create this on my own, i seem to get stuck on trying to get the foreach loop container. Thanks for you help.

  3. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

    • Hi Pinal, Could you please help me with this, I want to know who put the database offline and when? thanks for your help in advance ! Regards, Ane

  4. Hi Pinal ,
    Thank You Verymuch It is Working fine.
    Actually i am a data Base developer, when ever i come across with any doubts You are the only one person where i can get a solution. Please dont stop your Posting.
    thanks once again.
    Karikalan.s

  5. Hi Pinal,
    i want to know about Offline Reporting in ssrs. can you provide any links for that.how it is useful in ssrs. how can i do the offline reports in ssrs. please help me with any information.

  6. is necessary execute the complete query ? for example can i execute only the statment ALTER DATABASE [myDB] SET OFFLINE WITH
    ROLLBACK IMMEDIATE
    GO
    ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s