detach database, attach database without log file

[DBA]

If I detach a database on a server, then attach the database with a
single file only using the data file...will I lose any transactions
that were in the original log file? There is a debate going on here at
work where some people think that during the detach, transactions in
the log file are saved to the data file.

Thanks!

[John Bell]

Hi

You will not loose any committed transactions.

Uncommitted transactions will normally be rolled back rolled back even if
you attached the log file!! If this did happen it would be indicated in the
SQL Server log file.

John

"DBA" <ka******@yahoo.com> wrote in message
news:ff**************************@posting.google.c om...

If I detach a database on a server, then attach the database with a
single file only using the data file...will I lose any transactions
that were in the original log file? There is a debate going on here at
work where some people think that during the detach, transactions in
the log file are saved to the data file.

Thanks!

[Erland Sommarskog]

DBA (ka******@yahoo.com) writes:

If I detach a database on a server, then attach the database with a
single file only using the data file...will I lose any transactions
that were in the original log file? There is a debate going on here at
work where some people think that during the detach, transactions in
the log file are saved to the data file.

If the database is shut down cleanly, there will be nothing to lose.
If the database feels that there is something that needs to be rolled
forward or backward it will cry out for the log, and
sp_attach_db_single_file will not succeed. If you at this point to not
have the log file around anywhere, you are in trouble.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dan Guzman]

I believe sp_detach_db will ensure no transactions need to be rolled forward
or back. Consequently, the log isn't needed when attaching a database with
a single log file that was properly detached.

Attaching a database that wasn't properly detached is a crap shoot because
SQL Server might need the log to roll transactions forward/back. The
frequent newsgroup postings from folks who've tried this and failed are the
proof.

In any case, I'd keep the log and/or backups around until the database was
successfully attached.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

DBA (ka******@yahoo.com) writes:

If I detach a database on a server, then attach the database with a
single file only using the data file...will I lose any transactions
that were in the original log file? There is a debate going on here at
work where some people think that during the detach, transactions in
the log file are saved to the data file.

If the database is shut down cleanly, there will be nothing to lose.
If the database feels that there is something that needs to be rolled
forward or backward it will cry out for the log, and
sp_attach_db_single_file will not succeed. If you at this point to not
have the log file around anywhere, you are in trouble.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Christina Starr]

Thanks everyone for your contributions! I chose to shutdown the
application, backup the database, restore the database on the
destination, and then delete the original db. I felt that this was safer
than risking any problems during the detach.

Thanks for the info!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!