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

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Restoring to LSN

Author  Topic 

akoay
Starting Member

5 Posts

Posted - 2012-07-18 : 03:36:56
hi,
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)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-07-18 : 04:30:32
You can use STOPATMARK.

http://msdn.microsoft.com/en-us/library/ms191459.aspx
Go to Top of Page

akoay
Starting Member

5 Posts

Posted - 2012-07-18 : 18:36:38
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.

eg
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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-18 : 19:06:25
You can't skip the recovery process. It is there for data integrity reasons.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

akoay
Starting Member

5 Posts

Posted - 2012-07-18 : 22:45:45
SOLVED.
I had previously thought LSN 101 & 120 would be recorded into the _CT tables before the commit.
After reading http://msdn.microsoft.com/en-us/library/bb500305.aspx
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
   

- Advertisement -