Detaching system databases...

I need to detach (in order to move the datafiles) MASTER,
MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
options om SQL Server 2000 but it's saying "Can't detach
system databases". Am I missing a step?
Do I need to end all connections'
Thanks,
ToddWhy are you detaching the system databases? Are you planning on moving them
to another server? Here is some information about moving system databases,
that might help.
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
> I need to detach (in order to move the datafiles) MASTER,
> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
> options om SQL Server 2000 but it's saying "Can't detach
> system databases". Am I missing a step?
> Do I need to end all connections'
> Thanks,
> Todd|||Ugh, you can go through everything in KB #224071 and cross your fingers that
it all works well. Or, you can detach your *user* databases (or hopefully
you already have valid backups you can simply restore), uninstall SQL
Server, and then reinstall - placing the data directory where you should
have.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
> I need to detach (in order to move the datafiles) MASTER,
> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
> options om SQL Server 2000 but it's saying "Can't detach
> system databases". Am I missing a step?
> Do I need to end all connections'
> Thanks,
> Todd|||I want to move the datafiles because I put them on the C:\
(only 3GB) by mistake when I installed the SQL Server. I
want to move everything to another drive with 50GB+. I
have moved the user databases successfully but the system
databases don't want to go.
I reinstall would not be bad but I would prefer not to;)
>--Original Message--
>Why are you detaching the system databases? Are you
planning on moving them
>to another server? Here is some information about moving
system databases,
>that might help.
>http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;224071&Product=
=3Dsql2k
>-- >---=--
>---=--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Todd" <anonymous@.discussions.microsoft.com> wrote in message
>news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
>> I need to detach (in order to move the datafiles) MASTER,
>> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
>> options om SQL Server 2000 but it's saying "Can't detach
>> system databases". Am I missing a step?
>> Do I need to end all connections'
>> Thanks,
>> Todd
>
>.
>|||>> I reinstall would not be bad but I would prefer not to;)
Why? It's much cleaner, IMHO.|||KB 224071, which Aaron suggested, describes moving files for system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:f40501c43dc5$006531a0$a101280a@.phx.gbl...
I want to move the datafiles because I put them on the C:\
(only 3GB) by mistake when I installed the SQL Server. I
want to move everything to another drive with 50GB+. I
have moved the user databases successfully but the system
databases don't want to go.
I reinstall would not be bad but I would prefer not to;)
>--Original Message--
>Why are you detaching the system databases? Are you
planning on moving them
>to another server? Here is some information about moving
system databases,
>that might help.
>http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k
>--
>----
>----
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Todd" <anonymous@.discussions.microsoft.com> wrote in message
>news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
>> I need to detach (in order to move the datafiles) MASTER,
>> MSDB, MODEL, TEMPDB. I am using EM and the sp_detach
>> options om SQL Server 2000 but it's saying "Can't detach
>> system databases". Am I missing a step?
>> Do I need to end all connections'
>> Thanks,
>> Todd
>
>.
>

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
>