Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Restoring to LSN
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

5 Posts

Posted - 07/18/2012 :  03:36:56  Show Profile  Reply with Quote
i want to restore a database to a particular LSN without SQL Server performing the undo phase and rolling back uncomitted transactions (which would actually bring me to a different LSN point). Is that possible?

The reason i want to restore to a particular LSN point is because we have implemented CDC on production. A backup of production database is restore to a remote site (via usb drive). We then extract data out of the CDC _CT tables from the LSN point onwards and transfer that data to the remote site. This design was implemented due to the very limited network bandwith. (poor man's replication)

Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 07/18/2012 :  04:30:32  Show Profile  Reply with Quote
You can use STOPATMARK.
Go to Top of Page

Starting Member

5 Posts

Posted - 07/18/2012 :  18:36:38  Show Profile  Reply with Quote
thanks RickD
I am already using STOPAT to restore to LSN point. However when the WITH RECOVERY option is specified to make the database usable, SQL Server will perform rollback in the undo phase which will then bring me to a point prior to LSN.

LSN 100 start trxn A
LSN 101 trxn A does first insert
LSN 110 begin backup
LSN 120 trxn A does insert 2
LSN 130 end backup
LSN 135 trxn A does insert 3
LSN 140 end trxn A (commits)

When i restore the backup to LSN 130 at the remote site, i'm hoping that the two inserts (LSN 101 & 120) gets restored because i am capturing incremental changes on production from LSN 130 onwards (by extracting from CDC _CT tables).

Because of the loss of the two inserts (due to rollback by SQL Server) at the remote site, my table at the remote site becomes becomes different from the table in production.

It might not be possible not to rollback after a restore, but hoping someone can provide an elegant alternative. My current idea of a work around is to have complex checking of the _CT tables to enable extracting records further back in time.
Go to Top of Page

Almighty SQL Goddess

38200 Posts

Posted - 07/18/2012 :  19:06:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can't skip the recovery process. It is there for data integrity reasons.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server

Subscribe to my blog
Go to Top of Page

Starting Member

5 Posts

Posted - 07/18/2012 :  22:45:45  Show Profile  Reply with Quote
I had previously thought LSN 101 & 120 would be recorded into the _CT tables before the commit.
After reading
I now realised that only commited transactions get recorded into the _CT tables.

Hence from my example above,
even though the inserts @ LSN 101 & 120 gets rolled back after the restore to the remote site, it doesn't matter, because the inserts at LSN 101 & 102 does not get recorded into the _CT tables until @ LSN 140. I extract from the _CT table from the 'end_lsn' of the backup, which is 130, and the extract would contain the 3 inserts. After applying the extracted data to the remote site, my table would be consistent with production.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000