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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Update Statistics are Sampled By Default
Next Post
SQLAuthority News – Free Download – Microsoft SQL Server 2008 R2 RTM – Express with Management Tools – SQL Server 2008 R2 Books Online

Related Posts

23 Comments. Leave new

  • 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.

    Reply
    • Hi,
      I created a TestDB and used the GUI to mark it offline.
      Ran the ALTER Database command on the master database to mark the TestDB online, it worked fine.

      Use master
      GO
      ALTER DATABASE [TestDB] SET ONLINE
      GO

      Reply
    • Hi, I have exactly the same problem

      Reply
  • this worked

    Reply
  • 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.

    Reply
  • Question : Hello Pinal, How to find who brought the database offline ?

    Reply
    • 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

      Reply
  • Hello pinal
    Do i need to stop the SQL server services while taking the database offline

    Reply
  • 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

    Reply
  • Salam Pinal,
    Yes, its very simple but it is new thing for me…
    thanks

    Reply
  • thank you

    Reply
  • 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.

    Reply
  • Very useful
    Thanks

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

    Reply
  • My concern is this, when we set a database offline then whats necessary steps performed by SQL Engine.

    Please help me for the above query.
    Regards,
    Anurag Yadav

    Reply
  • hi pinal , when i tries to alter the dbname setting it to offline works fine when set to online throws me exception plz suggest me

    Reply
  • Thanks for your g8 help

    Reply
  • Vikrant Bhatt
    July 16, 2015 1:14 am

    Problem:

    We have one online database and it’s configure that on client sight
    but some time it’s suddenly disconnect from the client’s configuration and they are not able to configure again and have to call us.
    when we show that database in the SSMS then when click on that says error like
    you are not accessible that database
    at that time we have to that database first offline and then again put it online.

    What is the solution of that please help us
    I am very frustrated this problem now.

    Reply
  • Can you have a DDL in place to block a database from going offline?

    Reply
  • You dont give permissions to random people to take database offline in the first place (as he who has authority to do so can easily get around any constraint you specify)

    Reply
  • Hi Pinal,
    i have read all comments here and they are quite impressive. however, i have an issue with 2 databases out of 12 and they have same status – offlineread-onlysuspectemergency. I have tried your scripts and the contents became visible but not accessible. Please help.

    Reply

Leave a Reply

Menu