February 15, 2017 at 10:52 pm
Hi,
Can someone give me various ways to move about 200 Databases ( Total size 2 TB ) of data from source ( 32 bit Windows Server 2003 - with SQL Server 2008 R2 ) to be moved to the Destination with 64 bit hardware on Windows Server 2012 ( and SQL Server 2014 Ent ) ? Please note that the source server is on 24x 7 and we can bring it down only on Sunday for this major cutover.
For testing in Development , we just backed up all user databases at the sources and restored them on the destination using backup restore script and then ran consistency checks , Index Builds, Stats Updates , Updating Compatibility level to SQL Server 2014 ( 120 ) and the migration was successful. Had to exec the master..dbo.sp_help_revlogin which copied the logins and SIDs from source to destination ( no issues ) and had to manally copy the functions , user defined tables for our internal reporting housed on master and msdb. I found the system databases cannot be restored to higher version. The SQL agent wont start and the instance became unresponsive.
For Production, I wanted to do an intital backup at source ( all databases ), COPY the 2 TB data across the datacenters ( which will take a long time ) and do a RESTORE WITH NO RECOVERY, just on the cut off the day , CUT OFF ALL traffic to the source run a BACKUP DIFF and RESTORE the DIFF backup on destination WITH RECOVERY. That should sync both the source and destination. Any other ideas or gotcahs will be highly appreciated.
Thanks,
February 16, 2017 at 4:49 am
If you want the latest information:
Full backup production old -> new (can be done beforhand) with norecovery
Differential backup old ->new with no recovery
Taillog backup old ->new with recovery; the taillog backup will prevent new writes to the old database so all the latest changes are transferred to the new database
If you want some automation, check the powershellcommands from https://dbatools.io/functions/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply