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)
24 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.
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
Hi, I have exactly the same problem
this worked
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.
Question : Hello Pinal, How to find who brought the database offline ?
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
Hello pinal
Do i need to stop the SQL server services while taking the database offline
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
Salam Pinal,
Yes, its very simple but it is new thing for me…
thanks
thank you
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.
Very useful
Thanks
is necessary execute the complete query ? for example can i execute only the statment ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
?
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
Thanks for your g8 help
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.
Can you have a DDL in place to block a database from going offline?
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)
Very true.
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.
by mistake my DB went in single user mode and now it went to a different user which is not me .. now i cannot connect the DB. what to do.
The DB was in emergency mode already we were trying to fix it