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
-- Mike
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).
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...[vbcol=seagreen]
> Keith Kratochvil wrote:
that[vbcol=seagreen]
availability[vbcol=seagreen]
Information[vbcol=seagreen]
program
> 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:
>
> 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:
>
> 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
>