cannot open user default database, login failed 1

[mrsbean]

It's been a bad day. The default database was accidently dropped. I have a backup of it from another server. I can't open the server I want to open to do anything.

How can I fix this? I don't know my way around very well, so please give instructions for idiots. Thank you.

MrsBean

 

[mrdenny]

Log in with a sysadmin account through osql. Use the -d command. This will have it switch to the specified database on connect, and should bypass the missing database problem. You can then restore the missing database.

From a command line (replace "-U sa -P {password}" with "-E" to use your NT account to log in).

osql -S {servername} -d {database} -U sa -P {password}

That should log you in.
Now restore your database.

restore database {databasename} from disk='d:\path\to\backup\file.bak'
go

This should restore the missing database.

Let me know if you have any questions or problems.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[mrsbean]

Thanks, partial success.

I was able to gain access using the osql command prompt. I wasn't able to restore the database. Can you tell me how to reset the default database from the osql?

MrsBean

 

[mrdenny]

You will want to use the sp_defaultdb command.

sp_defaultdb @loginame='{login name}', @defdb='database'
go

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)