Prev: Passing a SQL Function to MS SqlHelper?
Next: 527333 Reserch anything, nerw content up to date all sites 56
From: aj on 21 Jan 2010 09:32
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


From: Erland Sommarskog on 21 Jan 2010 17:47
aj (ronald(a)mcdonalds.com) writes:
> 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?

Maybe a silly question, but why not just run a regular log backup, and
use that?



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: aj on 29 Jan 2010 12:56
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:
> aj (ronald(a)mcdonalds.com) writes:
>> 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?
>
> Maybe a silly question, but why not just run a regular log backup, and
> use that?



 | 
Pages: 1
Prev: Passing a SQL Function to MS SqlHelper?
Next: 527333 Reserch anything, nerw content up to date all sites 56