ServerA (source server) is running Windows server 2008 with SQL server 2005 SP4 which is hosting a 90GB database. ServerB (destination server) is fresh, scratch built VM running Windows server 2012 R2 with SQL server 2008 R2. I've researched various ways to migrate the database (backup/restore, log shipping, etc). Which is the easiest method to migrate this 90GB database? I have a 2 hour maintenance window to get this done. This is my first time being tasked with migrating a SQL database, so I do not know what to expect nor do I know of any gotchas that may occur during the process.Any tips/advice would be greatly appreciated.
If data speeds are not that great, I would restore the last known full backup (e.g. the one taken the previous night) using the WITH RECOVERY option. Before taking the older database offline, take a tail log backup. On the new server, you would just need to restore the most recent differential and log backups.
Once the restore is done, you would of course need to do post tasks like setting up security (logins/users), agent jobs, maintenance jobs, changing of the compatibility level, updating statistics, rebuild indexes, etc.