Determining Security: detaching

Showing posts with label detaching. Show all posts

Monday, March 19, 2012

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,
Todd
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...&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;)
=20
>--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...24071&Product=
=3Dsql2k
>--=20
>---=
--
>---=
--
>--
>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 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...&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...
>
>.
>

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 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/defaul...1&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;)
=20
>--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/defaul...224071&Product=
=3Dsql2k
>--=20
>---=
--
>---=
--
>--
>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 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 database
s.
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$006
531a0$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.
>]
>--
>----
-
>----
-
>--
>Need SQL Server Examples check out my website at
>[url]http://www.geocities.com/sqlserverexamples" target="_blank">http://support.microsoft.com/defaul...lserverexamples
>"Todd" <anonymous@.discussions.microsoft.com> wrote in message
>news:f1c901c43db7$4ed332f0$a401280a@.phx.gbl...
>
>.
>

Detaching replicated database

Hello there
I have database that i marked it before as publisher. Since then it showed
with hand near to it. And then i can't detach it.
I remove the publisher and it didn't help
What should i do for detach it now?
any help would be useful
Whell Paul
How can i get this book?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:27bd01c4af70$fd679b00$a301280a@.phx.gbl...
> I'm not too sure what stage the system is in - you
> mention you have removed the publisher - does this mean
> that you have disabled publishing, or removed the
> publication. I think it must be the latter. In this case
> try resetting the replication flags on the database in
> question. EG if it was Pubs:
> exec sp_dboption 'pubs','published',false
> exec sp_dboption 'pubs','merge publish',false
> After that you should be able to detack.
> HTH,
> Paul Ibison (SQL MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Oded,
I've been lucky enough to have a sort of advanced
preview, but to register for it when it comes out
publically (imminently AFAIK) have a look at this
address: http://www.nwsu.com/0974973602reg.html.
Apparently it will eventually be available on Amazon
etc. - Hilary will be able to give you more info (if he
doesn't see this thread then post a separate one in this
newsgroup FAO Hilary Cotter).
Regards,
Paul Ibison (SQL Server MVP)

Detaching MDF/LDF files to SQL Server 2005 running on MSCS

I am planning to do the following when migrating to SQL Server 2005 running
on MSCS. Are there any issues or gotchas I should know regarding these steps:
1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone server
using SAN as the storage for DB files )
2) Run DB backup ( SQL 2005 ) on stand alone server
3) Run migration scripts ( the build scripts changes 80% of the db schema )
4) Run DB backup ( SQL 2005 )
5) Detach the DB
6) Attach the DB on SQL Server 2005 running on MS Cluster environment ( same
DB files on SAN storage)
The problem is that we have limited servers and the database servers
currently used as production will become unavailable when we move these
machines to become nodes within the cluster environment.
--
Lito DIf you use SQL Server Logins (SQL Server authentication) you might want to
make sure that you create the account on the new server with the same SID as
on the current server.
--
Keith Kratochvil
"LITO" <anynomous@.msn.com> wrote in message
news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
>I am planning to do the following when migrating to SQL Server 2005 running
> on MSCS. Are there any issues or gotchas I should know regarding these
> steps:
> 1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone
> server
> using SAN as the storage for DB files )
> 2) Run DB backup ( SQL 2005 ) on stand alone server
> 3) Run migration scripts ( the build scripts changes 80% of the db
> schema )
> 4) Run DB backup ( SQL 2005 )
> 5) Detach the DB
> 6) Attach the DB on SQL Server 2005 running on MS Cluster environment (
> same
> DB files on SAN storage)
> The problem is that we have limited servers and the database servers
> currently used as production will become unavailable when we move these
> machines to become nodes within the cluster environment.
> --
> Lito D|||Thank you, Keith. Question, do you mean create the account on SQL Server
2005 then run sp_change_users_login? Can you give me specifics on how to do
this?
Thanks again.
--
Lito D
"Keith Kratochvil" wrote:
> If you use SQL Server Logins (SQL Server authentication) you might want to
> make sure that you create the account on the new server with the same SID as
> on the current server.
> --
> Keith Kratochvil
>
> "LITO" <anynomous@.msn.com> wrote in message
> news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
> >I am planning to do the following when migrating to SQL Server 2005 running
> > on MSCS. Are there any issues or gotchas I should know regarding these
> > steps:
> >
> > 1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone
> > server
> > using SAN as the storage for DB files )
> > 2) Run DB backup ( SQL 2005 ) on stand alone server
> > 3) Run migration scripts ( the build scripts changes 80% of the db
> > schema )
> > 4) Run DB backup ( SQL 2005 )
> > 5) Detach the DB
> > 6) Attach the DB on SQL Server 2005 running on MS Cluster environment (
> > same
> > DB files on SAN storage)
> >
> > The problem is that we have limited servers and the database servers
> > currently used as production will become unavailable when we move these
> > machines to become nodes within the cluster environment.
> > --
> > Lito D
>
>|||You only need to run sp_change_users_login if the SID for the login on the
server is different than the user's SID within the database.
To prevent this problem from happening on your new server you can specify
the SID during the CREATE LOGIN process on the SQL 2005 box (SID = is one of
the params that you can use during the CREATE LOGIN process)
This query should give you a list of sql logins and their SID on the SQL
2000 box:
select name, sid
from master..syslogins
where isntgroup = 0
--
Keith Kratochvil
"LITO" <anynomous@.msn.com> wrote in message
news:DDC1D8DD-9400-4E6F-AB3F-936111654344@.microsoft.com...
> Thank you, Keith. Question, do you mean create the account on SQL Server
> 2005 then run sp_change_users_login? Can you give me specifics on how to
> do
> this?
> Thanks again.
> --
> Lito D
>
> "Keith Kratochvil" wrote:
>> If you use SQL Server Logins (SQL Server authentication) you might want
>> to
>> make sure that you create the account on the new server with the same SID
>> as
>> on the current server.
>> --
>> Keith Kratochvil
>>
>> "LITO" <anynomous@.msn.com> wrote in message
>> news:8A203EF5-EC88-4455-A841-1021A63D1DEC@.microsoft.com...
>> >I am planning to do the following when migrating to SQL Server 2005
>> >running
>> > on MSCS. Are there any issues or gotchas I should know regarding these
>> > steps:
>> >
>> > 1) Restore SQL 2000 database to SQL Server 2005 machine ( stand alone
>> > server
>> > using SAN as the storage for DB files )
>> > 2) Run DB backup ( SQL 2005 ) on stand alone server
>> > 3) Run migration scripts ( the build scripts changes 80% of the db
>> > schema )
>> > 4) Run DB backup ( SQL 2005 )
>> > 5) Detach the DB
>> > 6) Attach the DB on SQL Server 2005 running on MS Cluster environment (
>> > same
>> > DB files on SAN storage)
>> >
>> > The problem is that we have limited servers and the database servers
>> > currently used as production will become unavailable when we move these
>> > machines to become nodes within the cluster environment.
>> > --
>> > Lito D
>>

Sunday, March 11, 2012

Detaching MDF/LDF files to SQL Server 2005 running on MSCS

detaching db and stopping services

Is the state of SQL Server 2000 files the same:
1) when you detach them (run sp_detach-db); and
2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
In other words, when you stop the service, are the db files utomatically
put in the detached state, as if you had run sp_detach-db on them ?
If not, what is the difference between the files when the service is
stopped, and when they are in the detached state ?
Thanks
-- MikeNo, they are not the same.
sp_detach_db ensures that your files can be attached.
Simply grabbing the files from a stopped instance will not guarantee that
you can recover them.
Are you trying to implement a backup routine? Do you want 100% availability
during the backups? Look into the Transact-SQL BACKUP command. Information
and examples can be found within Books Online (within the SQL Server program
group).
--
Keith
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>|||I do not think the state MUST be the same... When you detach a file, there
can be no in-process transactions.
I believe that stopping the service is more 'rude'... I don't think it waits
for transactions to complete. Stopping the service through SEM or the
Service manager does wait, stopping through control panel services or net
stop does not...
Even if the state were the same, remember that detach removes all references
from master as well..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>|||When you detach a database, a checkpoint happens in the database. That means
that all changed that have been made to datapages but that haven't been
written to the data file yet, are written to disk. All these changes are of
course already in the transaction log.
Shutting down SQL Services should perform a check point as well, but IIRC
the documentation in Books Online (see SHUTDOWN) is not complete at this
point. If the server or service fails, you will in most cases not have a
properly check pointed data file, and you will need the transaction log to
recover your database.
It is definitely recommended that if you want to attach a database to
another server, that you use sp_detachdb.
--
Jacco Schalkwijk
SQL Server MVP
"MikeF" <mrf@.sent.com> wrote in message
news:uSyKDRsfEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Is the state of SQL Server 2000 files the same:
> 1) when you detach them (run sp_detach-db); and
> 2) when you stop all SQL Server services, particularly MSSQL$SQL2000 ?
> In other words, when you stop the service, are the db files utomatically
> put in the detached state, as if you had run sp_detach-db on them ?
> If not, what is the difference between the files when the service is
> stopped, and when they are in the detached state ?
> Thanks
> -- Mike
>|||Wayne Snyder wrote:
> I do not think the state MUST be the same... When you detach a file, there
> can be no in-process transactions.
> I believe that stopping the service is more 'rude'... I don't think it waits
> for transactions to complete. Stopping the service through SEM or the
> Service manager does wait, stopping through control panel services or net
> stop does not...
> Even if the state were the same, remember that detach removes all references
> from master as well..
>
Thanks Wayne, that is very interesting.
Are you saying that IF the service MSSQL$SQL2000 is stopped using SQL
Server Service Manager, then the db files ARE in exactly the same state
as if they were detached using sp_detach_db '
I would appreciate your confirming this.
-- Mike|||Jacco Schalkwijk wrote:
> When you detach a database, a checkpoint happens in the database. That means
> that all changed that have been made to datapages but that haven't been
> written to the data file yet, are written to disk. All these changes are of
> course already in the transaction log.
> Shutting down SQL Services should perform a check point as well, but IIRC
> the documentation in Books Online (see SHUTDOWN) is not complete at this
> point. If the server or service fails, you will in most cases not have a
> properly check pointed data file, and you will need the transaction log to
> recover your database.
> It is definitely recommended that if you want to attach a database to
> another server, that you use sp_detachdb.
>
Thanks Jacco. What do you think of Wayne's point that if the service
MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
files ARE in exactly the same state as if they were detached using
sp_detach_db '
I understand that using sp_detach_db is the proper way to do it, and we
are doing it that way, but this is a peculiar configuration I am trying
to work with and I need to know if using SQL Server Service Manager to
stop the service is actually the same as using sp_detach_db, like Wayne
suggests.
Many thanks for your time.
-- Mike|||Keith Kratochvil wrote:
> No, they are not the same.
> sp_detach_db ensures that your files can be attached.
> Simply grabbing the files from a stopped instance will not guarantee that
> you can recover them.
> Are you trying to implement a backup routine? Do you want 100% availability
> during the backups? Look into the Transact-SQL BACKUP command. Information
> and examples can be found within Books Online (within the SQL Server program
> group).
>
Thanks Keith. What do you think of Wayne's point that if the service
MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
files ARE in exactly the same state as if they were detached using
sp_detach_db '
I am not doing a backup routine (or at least, I am the recommended way
using sp_detach_db) but I am working with a peculiar configuration with
some odd restraints, and and I need to know if using SQL Server Service
Manager to stop the service is actually the same as using sp_detach_db,
like Wayne suggests.
Many thanks for your time.
-- Mike|||I believe the safest option is to issue sp_detach_db.
I have read a few posts within the newsgroups along the lines of "I did not
detach my database (I copied the mdf and ldf files when the services were
stopped) and now I cannot attach my database. Help!"
I know that it should be possible to attach databases that have not been
explicitly detached, but if I wanted to make sure that I could recover
(attach) my databases I would issue a detach statement first.
--
Keith
"MikeF" <mrf@.sent.com> wrote in message
news:%23R1$2CufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Keith Kratochvil wrote:
> > No, they are not the same.
> > sp_detach_db ensures that your files can be attached.
> > Simply grabbing the files from a stopped instance will not guarantee
that
> > you can recover them.
> >
> > Are you trying to implement a backup routine? Do you want 100%
availability
> > during the backups? Look into the Transact-SQL BACKUP command.
Information
> > and examples can be found within Books Online (within the SQL Server
program
> > group).
> >
> Thanks Keith. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db '
> I am not doing a backup routine (or at least, I am the recommended way
> using sp_detach_db) but I am working with a peculiar configuration with
> some odd restraints, and and I need to know if using SQL Server Service
> Manager to stop the service is actually the same as using sp_detach_db,
> like Wayne suggests.
> Many thanks for your time.
> -- Mike
>|||Practically speaking shutting down the database (in whatever way) and using
sp_detach_db are NOT the same. Theoretically they more or less should be the
same, but attaching databases that haven't been explicitly detached has been
a hit-and-miss experience for a lot of users.
--
Jacco Schalkwijk
SQL Server MVP
"MikeF" <mrf@.sent.com> wrote in message
news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Jacco Schalkwijk wrote:
>> When you detach a database, a checkpoint happens in the database. That
>> means that all changed that have been made to datapages but that haven't
>> been written to the data file yet, are written to disk. All these changes
>> are of course already in the transaction log.
>> Shutting down SQL Services should perform a check point as well, but IIRC
>> the documentation in Books Online (see SHUTDOWN) is not complete at this
>> point. If the server or service fails, you will in most cases not have a
>> properly check pointed data file, and you will need the transaction log
>> to recover your database.
>> It is definitely recommended that if you want to attach a database to
>> another server, that you use sp_detachdb.
> Thanks Jacco. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db '
> I understand that using sp_detach_db is the proper way to do it, and we
> are doing it that way, but this is a peculiar configuration I am trying to
> work with and I need to know if using SQL Server Service Manager to stop
> the service is actually the same as using sp_detach_db, like Wayne
> suggests.
> Many thanks for your time.
> -- Mike
>|||IMO, it doesn't matter what anyone of us say (no disrespect to anyone here, I'm just making a point). What
matter is what the documentation say. And the documentation say that you are guaranteed to be able to attach a
db only if you detached it first. (Look up BOL for exact wording.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MikeF" <mrf@.sent.com> wrote in message news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...
> Jacco Schalkwijk wrote:
> > When you detach a database, a checkpoint happens in the database. That means
> > that all changed that have been made to datapages but that haven't been
> > written to the data file yet, are written to disk. All these changes are of
> > course already in the transaction log.
> >
> > Shutting down SQL Services should perform a check point as well, but IIRC
> > the documentation in Books Online (see SHUTDOWN) is not complete at this
> > point. If the server or service fails, you will in most cases not have a
> > properly check pointed data file, and you will need the transaction log to
> > recover your database.
> >
> > It is definitely recommended that if you want to attach a database to
> > another server, that you use sp_detachdb.
> >
> Thanks Jacco. What do you think of Wayne's point that if the service
> MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> files ARE in exactly the same state as if they were detached using
> sp_detach_db '
> I understand that using sp_detach_db is the proper way to do it, and we
> are doing it that way, but this is a peculiar configuration I am trying
> to work with and I need to know if using SQL Server Service Manager to
> stop the service is actually the same as using sp_detach_db, like Wayne
> suggests.
> Many thanks for your time.
> -- Mike
>|||MikeF,
I absolutely agree with all of the other posters... It was not my intention
to suggest that shutting down the service by any means is a replacement for
a proper detach...
It is possible that on some occasions you can attach a db file from a
properly shutdown server... However it is risky at best, and something you
try when you have no other alternative...
I though you were just in trouble, and had no options...
Sorry for the mini-controversy...if you INTEND to attach a database, then
ALWAYS properly detach, and NEVER assume that the shutdown is good enough -
it might be, and it might not be...!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OUlgeXvfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> IMO, it doesn't matter what anyone of us say (no disrespect to anyone
here, I'm just making a point). What
> matter is what the documentation say. And the documentation say that you
are guaranteed to be able to attach a
> db only if you detached it first. (Look up BOL for exact wording.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MikeF" <mrf@.sent.com> wrote in message
news:%23nPAyBufEHA.904@.TK2MSFTNGP09.phx.gbl...
> > Jacco Schalkwijk wrote:
> >
> > > When you detach a database, a checkpoint happens in the database. That
means
> > > that all changed that have been made to datapages but that haven't
been
> > > written to the data file yet, are written to disk. All these changes
are of
> > > course already in the transaction log.
> > >
> > > Shutting down SQL Services should perform a check point as well, but
IIRC
> > > the documentation in Books Online (see SHUTDOWN) is not complete at
this
> > > point. If the server or service fails, you will in most cases not have
a
> > > properly check pointed data file, and you will need the transaction
log to
> > > recover your database.
> > >
> > > It is definitely recommended that if you want to attach a database to
> > > another server, that you use sp_detachdb.
> > >
> >
> > Thanks Jacco. What do you think of Wayne's point that if the service
> > MSSQL$SQL2000 is stopped using SQL Server Service Manager, then the db
> > files ARE in exactly the same state as if they were detached using
> > sp_detach_db '
> >
> > I understand that using sp_detach_db is the proper way to do it, and we
> > are doing it that way, but this is a peculiar configuration I am trying
> > to work with and I need to know if using SQL Server Service Manager to
> > stop the service is actually the same as using sp_detach_db, like Wayne
> > suggests.
> >
> > Many thanks for your time.
> >
> > -- Mike
> >
>|||In article <u8crS8sfEHA.2028@.tk2msftngp13.phx.gbl>,
jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> When you detach a database, a checkpoint happens in the database. That means
> that all changed that have been made to datapages but that haven't been
> written to the data file yet, are written to disk. All these changes are of
> course already in the transaction log.
> Shutting down SQL Services should perform a check point as well, but IIRC
> the documentation in Books Online (see SHUTDOWN) is not complete at this
> point. If the server or service fails, you will in most cases not have a
> properly check pointed data file, and you will need the transaction log to
> recover your database.
> It is definitely recommended that if you want to attach a database to
> another server, that you use sp_detachdb.
What about my case:
I have a standby server that I constantly restore logs to. If I detach
the database in order to back it up, it gets recovered when I re-attach
and the log synchronization gets broken. The database never has any
transactions other than the ones that come in from the production
server's logs as I just use it for large custom queries. Every night I
stop the service, add the data and log to a zip and restart the service.
Isn't this effectively a backup?|||I am confused. Why not backup your live (production) database? After all,
you are almost there with your log-shipping routines? All you need to do is
backup the file(s) created by that process and you should have your database
backup.
--
Keith
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b83f83cb16eeba0989692@.news...
> In article <u8crS8sfEHA.2028@.tk2msftngp13.phx.gbl>,
> jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> > When you detach a database, a checkpoint happens in the database. That
means
> > that all changed that have been made to datapages but that haven't been
> > written to the data file yet, are written to disk. All these changes are
of
> > course already in the transaction log.
> >
> > Shutting down SQL Services should perform a check point as well, but
IIRC
> > the documentation in Books Online (see SHUTDOWN) is not complete at this
> > point. If the server or service fails, you will in most cases not have a
> > properly check pointed data file, and you will need the transaction log
to
> > recover your database.
> >
> > It is definitely recommended that if you want to attach a database to
> > another server, that you use sp_detachdb.
> What about my case:
> I have a standby server that I constantly restore logs to. If I detach
> the database in order to back it up, it gets recovered when I re-attach
> and the log synchronization gets broken. The database never has any
> transactions other than the ones that come in from the production
> server's logs as I just use it for large custom queries. Every night I
> stop the service, add the data and log to a zip and restart the service.
> Isn't this effectively a backup?|||In article <uUkfvT7fEHA.1428@.TK2MSFTNGP10.phx.gbl>,
sqlguy.back2u@.comcast.net says...
> I am confused. Why not backup your live (production) database? After all,
> you are almost there with your log-shipping routines? All you need to do is
> backup the file(s) created by that process and you should have your database
> backup.
My standby server is only connected by a T1 and I have about 2GB of log
per day (DB size=50GB). In two weeks of log shipping I have already
blown the standby database once. I basically want a backup of the local
copy (standby) that I can go back to if it gets corrupted. I already
backup the production database, but keep the copy at the datacenter.
I can't backup the standby database through SQL server because it is in
read-only mode. I can't detach and re-attach because it will recover
the database on the re-attach.|||Understood. I was assuming that the two servers were in the same building
connected by a fast network. That assumption was incorrect!
I suggest that you email your wish/request to the SQL Server development
team. sqlwish@.microsoft.com They might not respond to you, but they do
read the emails and our suggestions can make a difference!
--
Keith
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b8402b4afd7ebef989694@.news...
> In article <uUkfvT7fEHA.1428@.TK2MSFTNGP10.phx.gbl>,
> sqlguy.back2u@.comcast.net says...
> > I am confused. Why not backup your live (production) database? After
all,
> > you are almost there with your log-shipping routines? All you need to
do is
> > backup the file(s) created by that process and you should have your
database
> > backup.
> My standby server is only connected by a T1 and I have about 2GB of log
> per day (DB size=50GB). In two weeks of log shipping I have already
> blown the standby database once. I basically want a backup of the local
> copy (standby) that I can go back to if it gets corrupted. I already
> backup the production database, but keep the copy at the datacenter.
> I can't backup the standby database through SQL server because it is in
> read-only mode. I can't detach and re-attach because it will recover
> the database on the re-attach.|||In that case I would consider your options such as "It will probably work, but it isn't guaranteed".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brad" <brad@.seesigifthere.com> wrote in message news:MPG.1b8402b4afd7ebef989694@.news...
> In article <uUkfvT7fEHA.1428@.TK2MSFTNGP10.phx.gbl>,
> sqlguy.back2u@.comcast.net says...
> > I am confused. Why not backup your live (production) database? After all,
> > you are almost there with your log-shipping routines? All you need to do is
> > backup the file(s) created by that process and you should have your database
> > backup.
> My standby server is only connected by a T1 and I have about 2GB of log
> per day (DB size=50GB). In two weeks of log shipping I have already
> blown the standby database once. I basically want a backup of the local
> copy (standby) that I can go back to if it gets corrupted. I already
> backup the production database, but keep the copy at the datacenter.
> I can't backup the standby database through SQL server because it is in
> read-only mode. I can't detach and re-attach because it will recover
> the database on the re-attach.|||That should have been "the attach option" instead of "options".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ea6AWAKgEHA.3416@.TK2MSFTNGP09.phx.gbl...
> In that case I would consider your options such as "It will probably work, but it isn't guaranteed".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brad" <brad@.seesigifthere.com> wrote in message news:MPG.1b8402b4afd7ebef989694@.news...
> > In article <uUkfvT7fEHA.1428@.TK2MSFTNGP10.phx.gbl>,
> > sqlguy.back2u@.comcast.net says...
> > > I am confused. Why not backup your live (production) database? After all,
> > > you are almost there with your log-shipping routines? All you need to do is
> > > backup the file(s) created by that process and you should have your database
> > > backup.
> >
> > My standby server is only connected by a T1 and I have about 2GB of log
> > per day (DB size=50GB). In two weeks of log shipping I have already
> > blown the standby database once. I basically want a backup of the local
> > copy (standby) that I can go back to if it gets corrupted. I already
> > backup the production database, but keep the copy at the datacenter.
> >
> > I can't backup the standby database through SQL server because it is in
> > read-only mode. I can't detach and re-attach because it will recover
> > the database on the re-attach.
>

detaching db and stopping services

detaching db and attaching to another server

Using SS2000 SP4. I've detached a db from server A and been able to attach i
t
without any problem to server B. However, if I try to attach it to server C,
the database shows as read-only. The same thing happens with other databases
that I try to attach to server C.
Any idea why?
Thanks,
--
Dan D.Dan D. wrote:
> Using SS2000 SP4. I've detached a db from server A and been able to attach
it
> without any problem to server B. However, if I try to attach it to server
C,
> the database shows as read-only. The same thing happens with other databas
es
> that I try to attach to server C.
> Any idea why?
> Thanks,
> --
> Dan D.
Check to see that you are not putting your database data files in a
read only folder. I had read only to true by accident and got the same
symptoms you did. When you turn off read only, you will have to
re-attach.
Tony|||That was it. Thanks. Do you know what the minimum permissions are? I gave
full control to the user that the services are using but if the user doesn't
need full control, I'd rather reduce them.
--
Dan D.
"Tony" wrote:

> Dan D. wrote:
> Check to see that you are not putting your database data files in a
> read only folder. I had read only to true by accident and got the same
> symptoms you did. When you turn off read only, you will have to
> re-attach.
> Tony
>

detaching db and attaching to another server

Using SS2000 SP4. I've detached a db from server A and been able to attach it
without any problem to server B. However, if I try to attach it to server C,
the database shows as read-only. The same thing happens with other databases
that I try to attach to server C.
Any idea why?
Thanks,
--
Dan D.Dan D. wrote:
> Using SS2000 SP4. I've detached a db from server A and been able to attach it
> without any problem to server B. However, if I try to attach it to server C,
> the database shows as read-only. The same thing happens with other databases
> that I try to attach to server C.
> Any idea why?
> Thanks,
> --
> Dan D.
Check to see that you are not putting your database data files in a
read only folder. I had read only to true by accident and got the same
symptoms you did. When you turn off read only, you will have to
re-attach.
Tony|||That was it. Thanks. Do you know what the minimum permissions are? I gave
full control to the user that the services are using but if the user doesn't
need full control, I'd rather reduce them.
--
Dan D.
"Tony" wrote:
> Dan D. wrote:
> > Using SS2000 SP4. I've detached a db from server A and been able to attach it
> > without any problem to server B. However, if I try to attach it to server C,
> > the database shows as read-only. The same thing happens with other databases
> > that I try to attach to server C.
> >
> > Any idea why?
> >
> > Thanks,
> > --
> > Dan D.
> Check to see that you are not putting your database data files in a
> read only folder. I had read only to true by accident and got the same
> symptoms you did. When you turn off read only, you will have to
> re-attach.
> Tony
>

Detaching databse to move to another location on the same server

Hi,
I am trying to detach a database to move to another locaion on the same
machine
The database has replication. I get the following message:
Cannot detach database while being replicated.
Any Idea how to detach database without removing replication.
Thanks
Have you tried a backup and restore method instead ?
"Paul k" <Paul k@.discussions.microsoft.com> wrote in message
news:2B485CBB-DA5D-4E7A-BB8F-1F5EF9ADC5EE@.microsoft.com...
> Hi,
> I am trying to detach a database to move to another locaion on the same
> machine
> The database has replication. I get the following message:
> Cannot detach database while being replicated.
> Any Idea how to detach database without removing replication.
> Thanks
>
|||you can stop MSSQL Server, and then copy the files to the other server, Then
re attach them there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul k" <Paul k@.discussions.microsoft.com> wrote in message
news:2B485CBB-DA5D-4E7A-BB8F-1F5EF9ADC5EE@.microsoft.com...
> Hi,
> I am trying to detach a database to move to another locaion on the same
> machine
> The database has replication. I get the following message:
> Cannot detach database while being replicated.
> Any Idea how to detach database without removing replication.
> Thanks
>

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
>

Monday, March 19, 2012

Detaching system databases...

Detaching system databases...

Detaching system databases...

Detaching replicated database

Detaching MDF/LDF files to SQL Server 2005 running on MSCS

Sunday, March 11, 2012

Detaching MDF/LDF files to SQL Server 2005 running on MSCS

Detaching MDF/LDF files to SQL Server 2005 running on MSCS

detaching db and stopping services

detaching db and stopping services

detaching db and stopping services

detaching db and attaching to another server

detaching db and attaching to another server

Detaching databse to move to another location on the same server

detaching database if not shown in enterprise manager

detaching database if not shown in enterprise manager

detaching database if not shown in enterprise manager

Determining Security

Blog Archive

About Me