Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 7258 | Views in the last 30 days: 9
 
Related Articles
FORUM

connect Database server

connect Database server

FORUM

Default Database Drive Almost Full

Change Default Database Location

SCRIPT

Restore Server/Database users/roles

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

FORUM

database restore file name

database restore

FORUM

Default Database

Finding Default Database

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones