October 11, 2010 at 11:33 pm
i want to transfer 10GB database from Server 1 to Server 2. This takes approximatly 4 hours. So, i have decided to go for Diff. backup. I did restore both Full backup and Diff backup. There was no issue. My concern is, how to reply on the diff backup. No doubt, the diff database saves lots of time. Has anyone experienced any data loss in this approch? i dont how to verify the data consistency. Next, should i also restore the log files from log backup? What if i dont do the log backup & restore?
October 11, 2010 at 11:51 pm
jamesmario (10/11/2010)
My concern is, how to reply on the diff backup.
Reply?
Has anyone experienced any data loss in this approch?
Diff backups contain all data that changed between the time of the full backup and the time of the diff. It is impossible to lose any changes that occurred in that interval
Next, should i also restore the log files from log backup? What if i dont do the log backup & restore?
Depends. If you restore just the full and diff, the database is as-of the time of the diff backup. If you want to restore transactions that occurred after that, you'll need to restore logs as well.
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
October 12, 2010 at 5:22 am
Is this a one-time restore ? Or do you want the 2 databases regularly synchronized at some level ?
October 12, 2010 at 8:12 am
should be bidirectional. Any backup & restore script for both Full DB backup & Diff backup? I think, Diff backup should be done after the full backup. Is that correct? Here is my scenario.
Scenario 1:
Full DB backup - 12 pm
Diff. DB backu - 5 pm
Restore Full DB backup followed by Diff. DB backup
Scenario 2:
Full DB backup - 12 pm
Diff. DB backup - 5pm
Full DB backup - 8pm
Can i restore Diff. db backup on 8pm Full backup?...This is not true in reality. However, if someone tries to do it without knowing the time tag.
October 12, 2010 at 8:17 am
jamesmario (10/12/2010)
Any backup & restore script for both Full DB backup & Diff backup?
Have you looked in Books Online? (The SQL manual)
Can i restore Diff. db backup on 8pm Full backup?
No.
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
October 12, 2010 at 9:36 am
jamesmario (10/12/2010)
should be bidirectional.
Could you explain ? Your first post says Server1 to Server2. But bi-directional means both directions such as Server2 restored back to server1 ?
The more you can explain, the better answers you will get, especially because some people (like me) need things spelled out to understand the different situations.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply