SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

What is your default database set to?

By Ben Kubicek,

I had an interesting thing happen to me the other day.  I was logging into a test database.  I needed to restore a production backup to a test server's test database.  When I was restoring the database I checked a few settings:

  • Overwrite the existing database
  • Close existing connections to the destination database.

You can see these in the image below:

Normally this is not an issue, but on this day I didn't realize the database restore was going to fail because the hard drive on the test server was out of space.  Since I was replacing my test database, it was in a restoring mode and no longer existed. 

Here is where the default database thing came into play.  Since the test database was my default database, all sorts of things stopped working.  I could no longer connect to the SQL Server.  I didn't have access to the SA account or password, and I was now locked out of the test sql server.

Thankfully, the options button on the connect dialog box saved me.  When you click on the options button the Connection Properties tab opens up.  There is a connect to database dropdown where you can select a different database to login to. It is on the Connection Properties tab, as shown below:

Once I was able to login I changed my default database to master.  I was able to clean up some space on the test server and then properly restore my database to test.

I would guess this might stir some arguments over what should be set as the default database for a user.  It is nice to login and have the database you are planning on working in as your default, but it has its downside when restoring that database doesn't go as planned.

Total article views: 7394 | Views in the last 30 days: 1
Related Articles

connect Database server

connect Database server


Default database

What? The default database is one of the options when creating a login in SQL Server. This is the...


Default Database Drive Almost Full

Change Default Database Location


Restore Server/Database users/roles

Restore server/database users and roles from another server when we are restoring databases to anoth...


database restore file name

database restore