March 3, 2017 at 8:13 pm
Hi,
My primary server has about 40 400 GB ( Windows Server 2003 with SQL Server 2008 ) databases each which need to be moved to the destination: new 64 bit server (SQL Server 2014 with Windows Server 2012 ).
The maintenance window is from Friday night 7PM to Sunday night 7PM,
To not lose much time this is the plan, please add steps if I missed any.
I will run full backup on Thursday night 10:00 PM for all databases, copy the backup files to the destination and restore all of them with NO RECOVERY. I will have log backups at the primary backing up Tlog every 1 hour and a script to list of out all tlog backups. at the destination I just restore all tlog backups and lastly restore database with recovery.
Thanks !
March 4, 2017 at 2:47 am
sqlguy80 - Friday, March 3, 2017 8:13 PMHi,
My primary server has about 40 400 GB ( Windows Server 2003 with SQL Server 2008 ) databases each which need to be moved to the destination: new 64 bit server (SQL Server 2014 with Windows Server 2012 ).
The maintenance window is from Friday night 7PM to Sunday night 7PM,To not lose much time this is the plan, please add steps if I missed any.
I will run full backup on Thursday night 10:00 PM for all databases, copy the backup files to the destination and restore all of them with NO RECOVERY. I will have log backups at the primary backing up Tlog every 1 hour and a script to list of out all tlog backups. at the destination I just restore all tlog backups and lastly restore database with recovery.
Thanks !
Quick thought, you may want to simplify this by doing a differential backup so you don't have to restore multiple log backups.
😎
March 5, 2017 at 2:02 pm
Eirikur Eiriksson - Saturday, March 4, 2017 2:47 AMsqlguy80 - Friday, March 3, 2017 8:13 PMHi,
My primary server has about 40 400 GB ( Windows Server 2003 with SQL Server 2008 ) databases each which need to be moved to the destination: new 64 bit server (SQL Server 2014 with Windows Server 2012 ).
The maintenance window is from Friday night 7PM to Sunday night 7PM,To not lose much time this is the plan, please add steps if I missed any.
I will run full backup on Thursday night 10:00 PM for all databases, copy the backup files to the destination and restore all of them with NO RECOVERY. I will have log backups at the primary backing up Tlog every 1 hour and a script to list of out all tlog backups. at the destination I just restore all tlog backups and lastly restore database with recovery.
Thanks !
Quick thought, you may want to simplify this by doing a differential backup so you don't have to restore multiple log backups.
😎
I agree with this but take it one step further that will keep the downtime to a minimum. Yes, do the backups you planned. Make sure they're compressed backups for the sake of performance. Start doing restores on the new box with NO RECOVERY. About an hour before the scheduled move, disable any log file backup jobs you may have and take a DIF backup of all the databases. Restore these on the new box with NO RECOVERY, as well When it comes time to drop the hammer, have a script ready that will do "Tail Log Backups" of all the databases, which will also put all of the databases in a state where they can't be updated any further. Then immediately restore all of these "Tail Log Backups" on the new server WITH RECOVERY.
You also need to make sure that you have the logins that each database depends on already transferred to the new system prior to that final step or you'll end up with a lot of online databases that no one can get to.
As soon as all the databases have been recovered, immediately start your full backups on the new server and turn on the log file backups as soon as that's done. Make damned sure that the new backups do NOT overwrite the backups you restored from.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2017 at 10:04 pm
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply