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.