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)
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
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.
[...] (Read more here) [...]
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