SQL Server 2005 SP2 9.0.3054

I would like to copy/restore my DB (with Full Recovery Model) onto a
/different/ server. I would like to do this w/o losing any
transactions.

I am familiar with the notion of restoring my last DB backup onto the
destination server, then rolling forward any logs that were backed up
subsequent to that backup. I don't do differential backups.

My question is about the "tail of the log". I also want to roll forward
those transactions that have not yet been backed up to a log file on my
source server. How do I grab those transactions? I am confused by the
assortment of NO_TRUNCATE, COPY_ONLY, NORECOVERY, etc.. options on
BACKUP LOG.

I'm thinking one way is to (on my source DB):
ALTER DATABASE <myDB>
SET SINGLE_USER With ROLLBACK IMMEDIATE
this will prevent any further transactions.

Then a
BACKUP LOG [myDB] TO DISK = N'C:\wherever\tail.trn'
WITH NAME = N'myDB_backup_whatever', NO_TRUNCATE

The <myDB> source database is not damaged or offline in any way - its
perfectly online and OK, and I'd like to keep it that way in case I need
to back out of what I'm doing. I don't want my source DB to go into
restoring state.

Is this the right approach? Is there a better way?

Thanks in Advance
aj

aj (ronald (AT) mcdonalds (DOT) com) writes:
Maybe a silly question, but why not just run a regular log backup, and
use that?



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nothing silly at all, Erland...and that is exactly what I wound up doing.

I needed to spend a bit more time with the doc.. I just need to
remember that "tail of the log" implies that you want that last
group of transactions, AND the database is not available. I wasn't
thinking about how a BACKUP LOG not only generates a trans log file,
but also causes a checkpoint for dirty buffers to be flushed to disk.
You only use NO_TRUNCATE when this can't happen (i.e. when the database
is not available).

In my situation, a regular BACKUP LOG worked fine...

thanks

aj

Erland Sommarskog wrote: