SQL Server - What if the default database is taken OFFLINE or DETACHED?

Sometimes DBAs don't find any work and start poking SQL Server by doing some naughty operations on it. Out of them I've gone through a stupid work.

I connect to my SQL Server instance through default system administrator account SA. By default, the default database is master. But being lazy and to save database switching time I changed its default database to one of my user defined databases. But accidentally I have taken that database OFFLINE. Then my mind was blown away as I wasn't able to connect to my instance and unfortunately I didn't have any other login to access the server.

Then I contacted my senior DBAs and got a solution. Let me show you practically,

As I said, the default database for any login is Master by default. Log on to your instance using SA as we have discussed about it earlier in this post.

EXEC sp_defaultdb 'sa','AdventureWorksDW2012'

Executing the above system stored procedure changes the default database for the login SA from master to AdventureWorksDW2012. Now let's carry on with our experiment by taking it OFFLINE.

ALTER DATABASE AdventureWorksDW2012 SET OFFLINE

Now close the SQL Server Management Studio and re-open it. Login with the credentials of 'sa'. You will get the following error.

Now, resolving this error is a simple task. Just follow the following steps:

• Connect to Database Engine.
• When it prompts for user authentication, provide the details like Server Name, Authentication Mode, Username and Password.
• Now click on Options which expands the connection dialog box.
• At the top, you can find a tab by name Connection Properties. Click on that.
• In the text box CONNECT TO DATABASE, type master or any other database name you know, existing in the respective server.
• Now you can login using 'sa'. Then go to user properties and change the name of the default database.

That's all, your connection to the server is restored. Change the default database as soon as you log on.