April 11, 2011 at 10:49 am
Is differential backup same as transactional log?
Will this manual replication plan work?
The utimate goal is new instance = attach Dirty US instance + new instance differential backup/transactional log
In another word,
PacRim DB= (US DB + US few weeks new data) + PacRim few weeks new data via differential.
1.To be more specific, at Date1, we want to detach US DB, copy and attach to PacRim.
2.On Date1, also turn on differential backup transaction log on PacRim DB
3.Set Identity Odd# + 2x increments for PacRim Db
4.Set Identity Event# + 2x increments for US Db
5.After pilot user testing on PacRim Db went well, we then want to repeat this.
Few weeks after Date1, detach the existing PacRim DB.
6.Then detach US DB (Date1+few weeks new data), copy and attach to PacRim.
7.Then Restore the PacRim DB transaction log from Date1 onto the 2nd time Attached PacRim DB which is the US DB (Date1+few weeks new data).
Will task#7 Restore produce expected result for PacRim DB having both odd and even # Identity?
For illustration, User inserts 2 records with transaction log turned on.
“TableX from PacRim” (Identity ID seeded (1, 1 + 2x) => Odd # ID
ID, Name
1, A
3, C
“TableX from US” (Identity ID seeded (0, 0 + 2x) => Even# ID.
ID, Name
2, B
4, D
What will be the result after applying transaction log (insert A & C)
from “TableX from PacRim”
to “TableX from US”?
Expected Result
“TableX from US”?
ID, Name
2, B
4, D
1, A
3, C
Or
UnExpected Result
“TableX from US”?
ID, Name
2, B
4, D
6, A
8, C
Many Thanks,
Jeffrey
April 11, 2011 at 12:08 pm
Step 7 sounds like you want to apply an old transaction log backup to a newer database. That won't work, or am I misunderstanding ?
April 11, 2011 at 12:14 pm
You cannot restore anything (differential or transaction log) to a DB that's online. To restore a diff backup the DB has to be either Restoring or Standby, left in that state after a full backup.
In your proposed list, step 7 will give you the error 'Cannot restore as no files are ready to roll forward'
Differential and log backups are tightly tied to the backup chain. Diffs can only be restored when the full backup that they are based on has been restored with norecovery or standby. Log backups can only be restored if the DB was restored with norecovery or standby and using either a full or diff from right before the log backup, or the log backup before this one in the chain.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 12:54 pm
Thanks for your question.
I am planning to restore the transactional log of New Instance's new data on top of the (Old Instance with its New Data).
Combine data = (Old Instance with its New Data) + (New Instance + its New Data)
0. Both instances have same data
1. After few weeks, both instances have different data transactions.
(2 different transactional logs)
2. Dettach New Instance
3. Attach Old Instance with its New Data (no care for old instance transactional log b/c MDF has its data already!)
4. Restore only the new instance transactional log happen during few weeks of time passed ontop of the attached old instance!
Thanks,
Jeffrey
April 11, 2011 at 1:02 pm
Gail, thanks for the quick reply.
How about a little twist?
Instead of #3 below,
How about on another box, restore from the old instance full backup and then restore differential transactional log not belonging to the old instance?
To make it more clear,
I am planning to restore the transactional log of New Instance's new data on top of the (Old Instance with its New Data).
Combine data = (Old Instance with its New Data) + (New Instance + restore its New Data)
0. Both instances have same data
1. After few weeks, both instances have different data transactions.
(2 different transactional logs)
2. Dettach New Instance
3. Attach Old Instance with its New Data (no care for old instance transactional log b/c MDF has its data already!)
4. Restore only the new instance transactional log happen during few weeks of time passed on top of the attached old instance!
Thanks,
Jeffrey
April 11, 2011 at 1:22 pm
Obviously I wasn't clear enough.
A restore of a diff/log MUST immediately follow the restore of the full backup. (Restore, not attach) and that restore MUST leave the database in recovering or standby state.
You cannot restore a log to a DB that is online (as it would be after an attach). Since restore must be to a DB that's recovering or standby, no modifications can have been made to the target.
In both of those scenarios, your step 4 will fail with 'Cannot restore as no files are ready to roll forward'
Please go and do some reading on log backup, what they are and what they are not.
p.s. the log is not an optional piece of the database. You can't just discard it and expect no ramifications.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 1:32 pm
Sounds as though you want to use the restore function to manipulate data ... that's the opposite of it's purpose. A restore is designed to bring a database back to the exact state it was in previously at a specific point in time. You can't mix-and-match.
I'm not quite sure what you want to do, but working with individual tables, and staging data in temporary tables, and then merging table data might be a better approach.
Additionally you can restore a database from an earlier backup as a new name, such as restore a backup from USDB taken on 4-1-2011 to a new database called "USDB_20110401", then pull out the specific tables that you need and copy them into PacRim.
April 11, 2011 at 3:59 pm
Gail,
Thanks!
I got verbal confirmation that the Logical Sequence Number (LSN) would be different in each instance of the Database.
Hence, Restore will indeed gives us the Error you indicated.
Thanks,
Jeffrey
April 11, 2011 at 4:01 pm
For SQL Server 2005, use restore to another instance will not work b/c of the LSN error mention on my prior replied post.
For SQL Server 2008 or Denali, I heard it is possible.
As of SQL Server 2005, yes, we will explore Merge Replication.
Thanks,
Jeffrey
April 11, 2011 at 7:23 pm
keepintouch2b (4/11/2011)
For SQL Server 2005, use restore to another instance will not work b/c of the LSN error mention on my prior replied post.For SQL Server 2008 or Denali, I heard it is possible.
As of SQL Server 2005, yes, we will explore Merge Replication.
Thanks,
Jeffrey
You can restore a database to another instance, but you can't apply transaction logs from a different database to it.
I didn't suggest merge replication. It may be overkill for what you're trying to accomplish .... whatever that is.
Here's a good article about transaction logs:
April 11, 2011 at 11:34 pm
keepintouch2b (4/11/2011)
For SQL Server 2005, use restore to another instance will not work b/c of the LSN error mention on my prior replied post.For SQL Server 2008 or Denali, I heard it is possible.
All my comments apply to SQL 2005, 2008, 2008 R2 and Denali. What you are trying will not work, it's not the point of restores.
If you need to sync changes from another DB, look at RedGate's SQLCompare and SQLDataCompare
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply