Hello,
I have Sql server 2008R2 and planning to test the Sql server migration of Production databases but I am thinking to stop TLog Backup job during Backup and Restore as I don't need TLog backup and my Backup and Restore process might be done by that time. Is it hurt anything like Log sequence or breaking log chain or is it safe to disable (stop) and enable (Turn ON) during Migration process?
Thanks for help!
Unless you have very large log backups that strain your I/O system, I don't think it would hurt to leave the transaction log backup running during your migration. The only time I've disabled them during a migration was when I was manually log shipping the databases to a new server and needed to ensure the log chain was restored before cutting over.
If you're migrating to new hardware, it's always better to leave the databases intact on the old hardware with an intact transaction log backup chain, in case there's a problem with the new hardware, you can fail back to the old without losing any original data. Having log backups that cover that time period are additional insurance.
Thanks Robert.
My concern was only that nothing any impact which cause to break the log chain or log sequence during backup and restoration process although i am just running pre migration test to make sure as i will using Data Migration Utility so i don't know it's using COPY_ONLY Backup.
Thank You!
Yes, COPY_ONLY will keep the log chain intact in the database as well as the backup files. Just be careful that your transaction log doesn't grow too much (e.g., don't run index maintenance, etc.)
Robert,
Thanks once again.
Did you know that DMA use COPY_ONLY backup and then Restore?
I am just trying to make sure that during my trial test it won't break any log chain or sequence.
I haven't used DMA, didn't know it used COPY_ONLY, but that's a good safeguard.
As long as you keep all your log backup files, and don't change the database recovery model, your log chain will stay intact. If in doubt, make a new full database backup and keep that safe, and all subsequent log backups made after it.
Robert,
I will be taking FULL backup before migration but during my test I was thinking to disable log backup hourly job for while during restoration and then enable again once migration completed. We are taking every hour TLog backup.
Probably I don't need to restore log backup as I might done before that and also this is just drill test migration but making sure that it won't hurt or break any log chain or sequence.
No, that won't break anything, just as long as you don't encounter any excessive log activity/growth.
Thanks Robert for the update.
I will then just run DMA, leave FULL Recovery Model as it is, leave enable TLog backup job and let it finish DMA migration process.
I found that DMA is faster then regular Backup and Restore for Migration and also it does login migration so don't need to worry for any permission issue.
Appreciate your prompt feedback!