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.
| 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 |
|
|
akoay
Starting Member
5 Posts |
Posted - 2012-07-18 : 18:36:38
|
| thanks RickDI 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.egLSN 100 start trxn ALSN 101 trxn A does first insertLSN 110 begin backupLSN 120 trxn A does insert 2LSN 130 end backupLSN 135 trxn A does insert 3LSN 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. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
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.aspxI 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. |
|
|
|
|
|
|
|
|
Restoring to LSN