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.
>