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 |
|
bspeca
Starting Member
1 Post |
 Posted - 2014-02-03 : 14:09:49
|
| Hello,I am currently working with an Enterprise 2008r2 SQL Server that has a complex maintenance plan. The database is being migrated to a data center to exact same platform. Because of the size of the Database (270+GB)and the transfer is occurring over a VPN, I have been trying to do a manual compressed backup, and then a differential as testing continues. However, I am finding that this is not working because the when I attempt the restore on the new server, SQL is asking for the backup set used for the daily production backup performed on the server. Can someone provide me with an idea of how to create a separate compress plan, that I can use to restore and keep the test somewhat up to date and eventually be ready for production?Thanks in advance for any advice!Ben Speca |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-02-03 : 14:28:48
|
quote:
SQL is asking for the backup set used for the daily production backup performed on the server
Please provide the error messsage so that we can help. All you need is a full backup, differential and then the tlog chain. Whether or not you compress it is up to you. SQL doesn't care.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2014-02-04 : 15:13:54
|
| Differential backups are based on the full backup taken before the differential backup was performed. In your scenario, what you are essentially doing is the following:Full Backup 1Full Backup 2Diff Backup 1Full Backup 3You are then restoring to the new system 'Full Backup 1' with NORECOVERY and then attempting to restore 'Diff Backup 1'. This will not work because the differential backup is based on 'Full Backup 2'.You can just use the transaction log backups - and restore from your manual backup forward to current point in time. To keep the system up to date, you will then need to copy every transaction log backup until you are ready to cut-over.You might consider looking into database mirroring. Even over a VPN you should be able to get it set up - and that will eliminate the requirement to continue copying transaction log backups across and restoring them. Then again, that may just be too much time and effort for this project. |
|
|
|
|
|
|
Non-Standard backup for Migration