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)
 Non-Standard backup for Migration

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 1
Full Backup 2
Diff Backup 1
Full Backup 3

You 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.
Go to Top of Page
   

- Advertisement -