Deleted default database, now can't get in

Excuse my cross post but I wasn't sure which group was best.
I was cleaning up my SQL 2005 instance and deleted my default database
(obviously I forgot that i changed it to my default database), now I cannot
use the Studio Manager (it uses Windows Authentication), and I tried using
sqlcmd, but it says it won't accept remote connections, even though I set it
to accept them using the configuration manager.
Any one have any ideas of how I can rectify this situation? The only thing i
don't want to do, is lose the work i have already done on this, set up
replication and jobs, but if I have to I guess I'll have to.
Thanks,
NancyI'm not sure if the SQLCMD error about enabling remote connections is
related to the default database problem. Have you tried connecting with an
explicit database specification? For example:
sqlcmd -d master -E
Hope this helps.
Dan Guzman
SQL Server MVP
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>|||"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
>
Can you get in as a different login (say as an adminstrator)?
Problem is, with a default database, the login has no place to map to, so
for security purposes can't let you in.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
Well if you dropped the default database, about the only thing you can do is
restore from your backup.
Dropping the database deletes it and all the objects.
> Thanks,
> Nancy
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks, but I tried that it doesn't work either, I should have been more
specific in my information, but thanks for the quick reply.
Nancy
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
> I'm not sure if the SQLCMD error about enabling remote connections is
> related to the default database problem. Have you tried connecting with
> an explicit database specification? For example:
> sqlcmd -d master -E
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>|||How to I restore if I can't get in, even using the Administrative connection
gets a remote connection not allowed error using sqlcmd?
Thanks, though,
Nancy
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23I5M%23hHfHHA.3956@.TK2MSFTNGP03.phx.gbl...
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
> Can you get in as a different login (say as an adminstrator)?
> Problem is, with a default database, the login has no place to map to, so
> for security purposes can't let you in.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
> Well if you dropped the default database, about the only thing you can do
> is restore from your backup.
> Dropping the database deletes it and all the objects.
>
>> Thanks,
>> Nancy
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||You seem to be working from your own workstation. Can you log directly onto
the SQL Server machine and run SQLCMD from there?
RLF
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
> Nancy
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>> I'm not sure if the SQLCMD error about enabling remote connections is
>> related to the default database problem. Have you tried connecting with
>> an explicit database specification? For example:
>> sqlcmd -d master -E
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
>> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections, even
>> though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>>
>|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> Thanks, but I tried that it doesn't work either, I should have been more
> specific in my information, but thanks for the quick reply.
What does "does not work" mean? Do you get an error message? In such case
what?
I just tried setting the default database for a login, and then I dropped
that database. When I logged in with
sqlcmd -U frits -P xxxxx -S .\NELJÄ
this failed with "Cannot open user default database." as expected.
However, when I tried:
sqlcmd -U frits -P xxxxx -S .\NELJÄ -d tempdb
I was able to get in.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||This instance is on my workstation.
Thanks,
Nancy
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OBvqxnHfHHA.3648@.TK2MSFTNGP05.phx.gbl...
> You seem to be working from your own workstation. Can you log directly
> onto the SQL Server machine and run SQLCMD from there?
> RLF
> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
> news:%23In76iHfHHA.2308@.TK2MSFTNGP06.phx.gbl...
>> Thanks, but I tried that it doesn't work either, I should have been more
>> specific in my information, but thanks for the quick reply.
>> Nancy
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:47814D35-9C31-457F-A5BD-690CB4122E57@.microsoft.com...
>> I'm not sure if the SQLCMD error about enabling remote connections is
>> related to the default database problem. Have you tried connecting with
>> an explicit database specification? For example:
>> sqlcmd -d master -E
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
>> news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Excuse my cross post but I wasn't sure which group was best.
>> I was cleaning up my SQL 2005 instance and deleted my default database
>> (obviously I forgot that i changed it to my default database), now I
>> cannot use the Studio Manager (it uses Windows Authentication), and I
>> tried using sqlcmd, but it says it won't accept remote connections,
>> even though I set it to accept them using the configuration manager.
>> Any one have any ideas of how I can rectify this situation? The only
>> thing i don't want to do, is lose the work i have already done on this,
>> set up replication and jobs, but if I have to I guess I'll have to.
>> Thanks,
>> Nancy
>>
>>
>|||The server (my workstation is actively refusing the connection...may be
caused by the fact that by default SQL Server refuses remote connections - I
have set it up to accept remote connections.
This is the error I get when I tried your sqlcmd as well as the ones I tried
originally to get to the master db.
Nancy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99108DEBF1DDYazorman@.127.0.0.1...
> Nancy Lytle (nancy_lytle@.payformance.com) writes:
>> Thanks, but I tried that it doesn't work either, I should have been more
>> specific in my information, but thanks for the quick reply.
> What does "does not work" mean? Do you get an error message? In such case
> what?
> I just tried setting the default database for a login, and then I dropped
> that database. When I logged in with
> sqlcmd -U frits -P xxxxx -S .\NELJÄ
> this failed with "Cannot open user default database." as expected.
> However, when I tried:
> sqlcmd -U frits -P xxxxx -S .\NELJÄ -d tempdb
> I was able to get in.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Just to let everyone know the outcome, after trying everything i could think
of , and that you all could suggest I almost gave up.
But first thing this morning I downloaded a trial of EMS SQL Manager for
SQL Server and installed it, and with it I could get into the instance and
change my default database back to master.
Thank goodness, that saved me a reinstall and hours of work,
Again, thank you all for your suggestions,
Nancy
"Nancy Lytle" <nancy_lytle@.payformance.com> wrote in message
news:egQn3bHfHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Excuse my cross post but I wasn't sure which group was best.
> I was cleaning up my SQL 2005 instance and deleted my default database
> (obviously I forgot that i changed it to my default database), now I
> cannot use the Studio Manager (it uses Windows Authentication), and I
> tried using sqlcmd, but it says it won't accept remote connections, even
> though I set it to accept them using the configuration manager.
> Any one have any ideas of how I can rectify this situation? The only thing
> i don't want to do, is lose the work i have already done on this, set up
> replication and jobs, but if I have to I guess I'll have to.
> Thanks,
> Nancy
>|||Nancy Lytle (nancy_lytle@.payformance.com) writes:
> The server (my workstation is actively refusing the connection...may be
> caused by the fact that by default SQL Server refuses remote connections
> - I have set it up to accept remote connections. This is the error I get
> when I tried your sqlcmd as well as the ones I tried originally to get
> to the master db.
Obviously the problem is something else than the missing default database.
If I understand your post correctly, the SQL Server is on your local
machine. If you then get the error "actively refused the connection",
I would recommend that you restart the SQL Server service, to see if
this helps.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx