I have to move a database from one cloud provider to another. It has to be done in an hour window. The DB is 16Gb, and the line speed between the current host, and Azure (The final destination) equates to a 30 hour backup file copy. Far exceeding the time we have.
I wanted to do transnational replication, and once the main data volume is replicated over a few days, simply switch the website to use the replicated database, and kill the source.
Unfortunately, the database we are inheriting has numerous issues. Views are broken (Reference deleted tables/columns). And a lot of columns have no primary keys. (Please note, not my database. Well, it is now.. but..)
So, replication fails to even start because of the views, and then won't replicate tables with no PK.
My other option would to be do backups, and incremental backups. But do incremental backups split into separate files? If so, I could backup to a folder on the source, then move the large initial backup, and then move the incremental updates. Is that an option? Will incremental backups give me smaller files? I'd then be able to move the files over a period, and then on go live day, run the final backup, and move the small incremental file.
Best Answer
Absolutely, that is an option. Do the full backup, and then log backups. At the receiving end, restore all but the last using NORECOVERY.
Automating regular log backups and then restoring at the other end is what we call "log shipping", and there's a feature to do that using Agent jobs in SQL Server. Might, or might now, be relevant for you depending on your setup. Possibly not because of your cloud environment (it uses a share to store the backup files, which the originating server needs access to.
Whether to then stripe one backup over several files (not sure if you had this in mind), depends on whether it is beneficial to have several files for the transfer process. I doubt you gain much with striping for the log backups.