detaching database if not shown in enterprise manager

Is there anyway to detach a database when the database no
longer shows in enterprise manager? I am assuming that
their may be a sp_detach or something, is this correct.
Thanks in advanceSteven Scaife wrote:
> Is there anyway to detach a database when the database no
> longer shows in enterprise manager? I am assuming that
> their may be a sp_detach or something, is this correct.
Yes, that is correct. The system stored procedure is sp_detach_db. Look
up the exact syntax in BOL.
HTH,
Andrés Taylor|||When you have a question like that you may want to try looking in
BooksOnLine first. It can save you a lot of time<g>. There is indeed
stored procedures called sp_detach_db and sp_attach_db that you can use.
--
Andrew J. Kelly SQL MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:096101c48f5c$82f935b0$a401280a@.phx.gbl...
> Is there anyway to detach a database when the database no
> longer shows in enterprise manager? I am assuming that
> their may be a sp_detach or something, is this correct.
> Thanks in advance|||Thanks i'll know to use BOL now, just recently started working with SQL
server just coming to end of my first month, so not up on the help files
available, so trying to research ways of getting out of this problem we are
in.
When i run sp_detach_db i get the following error
Server: Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
The database 'swordfish' does not exist. Use sp_helpdb to show available
databases.
when i use sp_helpdb it doesn't appear in the list, any way of removing it
when it won't show in enterprise manager or that list
thanks in advance
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ej2%23921jEHA.1800@.TK2MSFTNGP15.phx.gbl...
> When you have a question like that you may want to try looking in
> BooksOnLine first. It can save you a lot of time<g>. There is indeed
> stored procedures called sp_detach_db and sp_attach_db that you can use.
> --
> Andrew J. Kelly SQL MVP
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:096101c48f5c$82f935b0$a401280a@.phx.gbl...
> > Is there anyway to detach a database when the database no
> > longer shows in enterprise manager? I am assuming that
> > their may be a sp_detach or something, is this correct.
> >
> > Thanks in advance
>|||I would have assumed that if the server is registered then all the databases
would be shown when using Enterprise Manager.
Could it be that they are already detached (can you attach them)?|||No i cant attach using sp_attach_db or sp_attach_single_file_db. The SQL
process was stopped this morning then the LDF file for the database was
deleted by one of the IT staff but the database wasn't detached so we have a
65 gig MDF file that can't be re-attached, thinking of running SP3a but SP3
is already installed and am unsure if this will fix the problem. I get
error 9004 when trying to attach the database through the right click
context menu, and broken link using the sp_attach. in QA.
"Griff" <Howling@.The.Moon> wrote in message
news:uGRdFA2jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> I would have assumed that if the server is registered then all the
databases
> would be shown when using Enterprise Manager.
> Could it be that they are already detached (can you attach them)?
>|||Have you tried sp_attach_single_db?
"Steven Scaife" <sp@.nospam.com> wrote in message
news:uqCL%23J2jEHA.1800@.TK2MSFTNGP15.phx.gbl...
> No i cant attach using sp_attach_db or sp_attach_single_file_db. The SQL
> process was stopped this morning then the LDF file for the database was
> deleted by one of the IT staff but the database wasn't detached so we have
a
> 65 gig MDF file that can't be re-attached, thinking of running SP3a but
SP3
> is already installed and am unsure if this will fix the problem. I get
> error 9004 when trying to attach the database through the right click
> context menu, and broken link using the sp_attach. in QA.
> "Griff" <Howling@.The.Moon> wrote in message
> news:uGRdFA2jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> > I would have assumed that if the server is registered then all the
> databases
> > would be shown when using Enterprise Manager.
> >
> > Could it be that they are already detached (can you attach them)?
> >
> >
>|||Here are some links that you may want to browse:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Steven Scaife" <sp@.nospam.com> wrote in message
news:uqCL%23J2jEHA.1800@.TK2MSFTNGP15.phx.gbl...
> No i cant attach using sp_attach_db or sp_attach_single_file_db. The SQL
> process was stopped this morning then the LDF file for the database was
> deleted by one of the IT staff but the database wasn't detached so we have
a
> 65 gig MDF file that can't be re-attached, thinking of running SP3a but
SP3
> is already installed and am unsure if this will fix the problem. I get
> error 9004 when trying to attach the database through the right click
> context menu, and broken link using the sp_attach. in QA.
> "Griff" <Howling@.The.Moon> wrote in message
> news:uGRdFA2jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> > I would have assumed that if the server is registered then all the
> databases
> > would be shown when using Enterprise Manager.
> >
> > Could it be that they are already detached (can you attach them)?
> >
> >
>

detaching database if not shown in enterprise manager

Is there anyway to detach a database when the database no
longer shows in enterprise manager? I am assuming that
their may be a sp_detach or something, is this correct.
Thanks in advance
Steven Scaife wrote:
> Is there anyway to detach a database when the database no
> longer shows in enterprise manager? I am assuming that
> their may be a sp_detach or something, is this correct.
Yes, that is correct. The system stored procedure is sp_detach_db. Look
up the exact syntax in BOL.
HTH,
Andrs Taylor
|||When you have a question like that you may want to try looking in
BooksOnLine first. It can save you a lot of time<g>. There is indeed
stored procedures called sp_detach_db and sp_attach_db that you can use.
Andrew J. Kelly SQL MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:096101c48f5c$82f935b0$a401280a@.phx.gbl...
> Is there anyway to detach a database when the database no
> longer shows in enterprise manager? I am assuming that
> their may be a sp_detach or something, is this correct.
> Thanks in advance
|||Thanks i'll know to use BOL now, just recently started working with SQL
server just coming to end of my first month, so not up on the help files
available, so trying to research ways of getting out of this problem we are
in.
When i run sp_detach_db i get the following error
Server: Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
The database 'swordfish' does not exist. Use sp_helpdb to show available
databases.
when i use sp_helpdb it doesn't appear in the list, any way of removing it
when it won't show in enterprise manager or that list
thanks in advance
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ej2%23921jEHA.1800@.TK2MSFTNGP15.phx.gbl...
> When you have a question like that you may want to try looking in
> BooksOnLine first. It can save you a lot of time<g>. There is indeed
> stored procedures called sp_detach_db and sp_attach_db that you can use.
> --
> Andrew J. Kelly SQL MVP
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:096101c48f5c$82f935b0$a401280a@.phx.gbl...
>
|||I would have assumed that if the server is registered then all the databases
would be shown when using Enterprise Manager.
Could it be that they are already detached (can you attach them)?
|||No i cant attach using sp_attach_db or sp_attach_single_file_db. The SQL
process was stopped this morning then the LDF file for the database was
deleted by one of the IT staff but the database wasn't detached so we have a
65 gig MDF file that can't be re-attached, thinking of running SP3a but SP3
is already installed and am unsure if this will fix the problem. I get
error 9004 when trying to attach the database through the right click
context menu, and broken link using the sp_attach. in QA.
"Griff" <Howling@.The.Moon> wrote in message
news:uGRdFA2jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> I would have assumed that if the server is registered then all the
databases
> would be shown when using Enterprise Manager.
> Could it be that they are already detached (can you attach them)?
>
|||Have you tried sp_attach_single_db?
"Steven Scaife" <sp@.nospam.com> wrote in message
news:uqCL%23J2jEHA.1800@.TK2MSFTNGP15.phx.gbl...
> No i cant attach using sp_attach_db or sp_attach_single_file_db. The SQL
> process was stopped this morning then the LDF file for the database was
> deleted by one of the IT staff but the database wasn't detached so we have
a
> 65 gig MDF file that can't be re-attached, thinking of running SP3a but
SP3
> is already installed and am unsure if this will fix the problem. I get
> error 9004 when trying to attach the database through the right click
> context menu, and broken link using the sp_attach. in QA.
> "Griff" <Howling@.The.Moon> wrote in message
> news:uGRdFA2jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> databases
>
|||Here are some links that you may want to browse:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Steven Scaife" <sp@.nospam.com> wrote in message
news:uqCL%23J2jEHA.1800@.TK2MSFTNGP15.phx.gbl...
> No i cant attach using sp_attach_db or sp_attach_single_file_db. The SQL
> process was stopped this morning then the LDF file for the database was
> deleted by one of the IT staff but the database wasn't detached so we have
a
> 65 gig MDF file that can't be re-attached, thinking of running SP3a but
SP3
> is already installed and am unsure if this will fix the problem. I get
> error 9004 when trying to attach the database through the right click
> context menu, and broken link using the sp_attach. in QA.
> "Griff" <Howling@.The.Moon> wrote in message
> news:uGRdFA2jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> databases
>

detaching database if not shown in enterprise manager

Is there anyway to detach a database when the database no
longer shows in enterprise manager? I am assuming that
their may be a sp_detach or something, is this correct.
Thanks in advanceSteven Scaife wrote:
> Is there anyway to detach a database when the database no
> longer shows in enterprise manager? I am assuming that
> their may be a sp_detach or something, is this correct.
Yes, that is correct. The system stored procedure is sp_detach_db. Look
up the exact syntax in BOL.
HTH,
Andrs Taylor|||When you have a question like that you may want to try looking in
BooksOnLine first. It can save you a lot of time<g>. There is indeed
stored procedures called sp_detach_db and sp_attach_db that you can use.
Andrew J. Kelly SQL MVP
"Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
news:096101c48f5c$82f935b0$a401280a@.phx.gbl...
> Is there anyway to detach a database when the database no
> longer shows in enterprise manager? I am assuming that
> their may be a sp_detach or something, is this correct.
> Thanks in advance|||Thanks i'll know to use BOL now, just recently started working with SQL
server just coming to end of my first month, so not up on the help files
available, so trying to research ways of getting out of this problem we are
in.
When i run sp_detach_db i get the following error
Server: Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 25
The database 'swordfish' does not exist. Use sp_helpdb to show available
databases.
when i use sp_helpdb it doesn't appear in the list, any way of removing it
when it won't show in enterprise manager or that list
thanks in advance
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ej2%23921jEHA.1800@.TK2MSFTNGP15.phx.gbl...
> When you have a question like that you may want to try looking in
> BooksOnLine first. It can save you a lot of time<g>. There is indeed
> stored procedures called sp_detach_db and sp_attach_db that you can use.
> --
> Andrew J. Kelly SQL MVP
>
> "Steven Scaife" <anonymous@.discussions.microsoft.com> wrote in message
> news:096101c48f5c$82f935b0$a401280a@.phx.gbl...
>|||I would have assumed that if the server is registered then all the databases
would be shown when using Enterprise Manager.
Could it be that they are already detached (can you attach them)?|||No i cant attach using sp_attach_db or sp_attach_single_file_db. The SQL
process was stopped this morning then the LDF file for the database was
deleted by one of the IT staff but the database wasn't detached so we have a
65 gig MDF file that can't be re-attached, thinking of running SP3a but SP3
is already installed and am unsure if this will fix the problem. I get
error 9004 when trying to attach the database through the right click
context menu, and broken link using the sp_attach. in QA.
"Griff" <Howling@.The.Moon> wrote in message
news:uGRdFA2jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> I would have assumed that if the server is registered then all the
databases
> would be shown when using Enterprise Manager.
> Could it be that they are already detached (can you attach them)?
>|||Have you tried sp_attach_single_db?
"Steven Scaife" <sp@.nospam.com> wrote in message
news:uqCL%23J2jEHA.1800@.TK2MSFTNGP15.phx.gbl...
> No i cant attach using sp_attach_db or sp_attach_single_file_db. The SQL
> process was stopped this morning then the LDF file for the database was
> deleted by one of the IT staff but the database wasn't detached so we have
a
> 65 gig MDF file that can't be re-attached, thinking of running SP3a but
SP3
> is already installed and am unsure if this will fix the problem. I get
> error 9004 when trying to attach the database through the right click
> context menu, and broken link using the sp_attach. in QA.
> "Griff" <Howling@.The.Moon> wrote in message
> news:uGRdFA2jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> databases
>|||Here are some links that you may want to browse:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Steven Scaife" <sp@.nospam.com> wrote in message
news:uqCL%23J2jEHA.1800@.TK2MSFTNGP15.phx.gbl...
> No i cant attach using sp_attach_db or sp_attach_single_file_db. The SQL
> process was stopped this morning then the LDF file for the database was
> deleted by one of the IT staff but the database wasn't detached so we have
a
> 65 gig MDF file that can't be re-attached, thinking of running SP3a but
SP3
> is already installed and am unsure if this will fix the problem. I get
> error 9004 when trying to attach the database through the right click
> context menu, and broken link using the sp_attach. in QA.
> "Griff" <Howling@.The.Moon> wrote in message
> news:uGRdFA2jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> databases
>

Detach database greyed out

I'm trying to detach a database and reattach to another server. When trying to detach it using Enterprise Manager, the command is greyed out. I've tried taking the database offline first but the command is still not enabled. I'm logged in as sysadmin. What gives?Why not use SP_DETACHDB From query analyzer?
And its better to use such admin. statements using QA than depending upon GUI>|||Is it one on the default dbs you are trying to detach? If it is, I don't think you can! There are ways around it though(copy the data and log files to where you want the new db), not supported though I suspect.|||Originally posted by Satya
Why not use SP_DETACHDB From query analyzer?
And its better to use such admin. statements using QA than depending upon GUI>

Satya,
I've tried this (SQL 2000) and I get an error message:

Database is in use. I can't figure out how to get it "out of use" ?

The actual database is on SQL 7 though...

..no, it's not one of the system databases but thanks for the tip!|||Then check the following :
use master
go
sp_who
... and check what process are using this database.
ANd kill those connections (SPID) using KILL statement and also keep the database in DBO use only just in case if any new connection/user will try to access.|||Originally posted by Satya
Then check the following :
use master
go
sp_who
... and check what process are using this database.
ANd kill those connections (SPID) using KILL statement and also keep the database in DBO use only just in case if any new connection/user will try to access.

DBO use only...you mean single user mode? SQL 7 doesn't support dbo only or, at least I can't find how to do that.|||Yes I was referring about DBO Use only.
You can do it from Enterprise Manager select the database properties and goto options tab to set it.
From Query analyzer you can set it using SP_DBOPTION, refer to books online for syntax and information.|||Originally posted by Satya
Yes I was referring about DBO Use only.
You can do it from Enterprise Manager select the database properties and goto options tab to set it.
From Query analyzer you can set it using SP_DBOPTION, refer to books online for syntax and information.

OK, the database is set to DBO use only. I'm working with the 2000 version of the database since the attach/detach doesn't seem to be supported at all in 7. Anyway, even with the DBO use only set, the cmd is still greyed out.

Design Query in Editor (SSMS)

In Enterprise Manager(EM): click a table then open table in Query mode. A
user can drag and drop tables from different databases. Once a user connects
a common ID the tool will automatically create the JOINS for you.
Question--
In SSMS: 'Design Query in Editor' (Ctl+Shift+Q) does not allow a user to do
the same functionality described in the EM above. Is there a certain way to
do this besides typing it the query window?Open the Query Designer (Click on the "New Query" button).
On the toolbar just over the frame you use to write your queries, you find
these buttons (from let to right):
-Connect
-Disconnect,
-Change Connection
-Combo to select the Database to be used
-Execute
-Check syntax
-stop execution
-Display Estimated query Plan
-Analyze query in Database Engine Tuning Advisor
-DESIGN QUERY IN EDITOR (the button with the classic symbol for Microsoft
design mode...)
-...etc, etc.
Have a good job
Gilberto Zampatti
"morphius" wrote:
> In Enterprise Manager(EM): click a table then open table in Query mode. A
> user can drag and drop tables from different databases. Once a user connects
> a common ID the tool will automatically create the JOINS for you.
> Question--
> In SSMS: 'Design Query in Editor' (Ctl+Shift+Q) does not allow a user to do
> the same functionality described in the EM above. Is there a certain way to
> do this besides typing it the query window?|||But the DESIGN QUERY IN EDITOR ( the classic symbol for Microsoft
> design mode...) does not allow a user to do the functionality described below?
"Gilberto Zampatti" wrote:
> Open the Query Designer (Click on the "New Query" button).
> On the toolbar just over the frame you use to write your queries, you find
> these buttons (from let to right):
> -Connect
> -Disconnect,
> -Change Connection
> -Combo to select the Database to be used
> -Execute
> -Check syntax
> -stop execution
> -Display Estimated query Plan
> -Analyze query in Database Engine Tuning Advisor
> -DESIGN QUERY IN EDITOR (the button with the classic symbol for Microsoft
> design mode...)
> -...etc, etc.
> Have a good job
> Gilberto Zampatti
> "morphius" wrote:
> > In Enterprise Manager(EM): click a table then open table in Query mode. A
> > user can drag and drop tables from different databases. Once a user connects
> > a common ID the tool will automatically create the JOINS for you.
> >
> > Question--
> > In SSMS: 'Design Query in Editor' (Ctl+Shift+Q) does not allow a user to do
> > the same functionality described in the EM above. Is there a certain way to
> > do this besides typing it the query window?|||The behavior will be slightly different, but - for example - if you drag two
tables and connect two fields (with or without the same name), the designer
proposes automaticllay an INNER JOIN.
What do you need exactly?
Gilberto Zampatti
"morphius" wrote:
> But the DESIGN QUERY IN EDITOR ( the classic symbol for Microsoft
> > design mode...) does not allow a user to do the functionality described below?
>
> "Gilberto Zampatti" wrote:
> > Open the Query Designer (Click on the "New Query" button).
> > On the toolbar just over the frame you use to write your queries, you find
> > these buttons (from let to right):
> > -Connect
> > -Disconnect,
> > -Change Connection
> > -Combo to select the Database to be used
> > -Execute
> > -Check syntax
> > -stop execution
> > -Display Estimated query Plan
> > -Analyze query in Database Engine Tuning Advisor
> > -DESIGN QUERY IN EDITOR (the button with the classic symbol for Microsoft
> > design mode...)
> > -...etc, etc.
> > Have a good job
> > Gilberto Zampatti
> > "morphius" wrote:
> >
> > > In Enterprise Manager(EM): click a table then open table in Query mode. A
> > > user can drag and drop tables from different databases. Once a user connects
> > > a common ID the tool will automatically create the JOINS for you.
> > >
> > > Question--
> > > In SSMS: 'Design Query in Editor' (Ctl+Shift+Q) does not allow a user to do
> > > the same functionality described in the EM above. Is there a certain way to
> > > do this besides typing it the query window?