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
-- Take the Database Offline
-- Take the Database Online
-- Clean up

Joyesh let me know if this answers your question.

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

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


  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.


  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


  7. Pingback: Interview Question of the Week #016 – How to Take Database Offline | Journey to SQL Authority with Pinal Dave

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


  9. 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)


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