Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Recently I got into a bit of a pickle. I was detaching some databases for a demo, which is something I do periodically to make it easier for someone to see what’s doing on. The database detached fine, and I ran my demo.
Then, as a sysadmin, I right clicked to attach a database back.
and all of a sudden I get this error:
Interesting. I could have attached the database back from the command line, or with dbatools, but I thought this was interesting. As I go to the attach dialog, a new connection is made. However, in this case, the default database for my sysadmin account was the one I’d detached.
Fortunately, this is easy to fix. First, I opened a query window with master as the specific database:
Then, I have a couple options to reset my default database. For old SQL Servers, many of you might have used sp_defaultdb. That’s marked as a deprecated procedure, so ALTER LOGIN is the new way. The syntax uses the WITH to include various options. In my case, I needed the DEFAULT_DATABASE item. This was my code:
ALTER LOGIN [PLATO\Steve] WITH DEFAULT_DATABASE = MASTER
If you are on an older version, something like this will work:
exec sp_defaultdb @login = ‘Steve’, @defaultdb = ‘master’
Once that was done, the GUI dialog worked. A quick and easy fix in this case.
As soon as I found the error, I knew what was happening. Resetting the default database took less than a minute, but I decided to spend 10 grabbing a few screenshots and putting this post together.
You could do the same thing. Show that you can recover from errors.