How to migrate sql server (large db size) and retain all settings and backup/job history?

Question

I am looking at migrating sql server with 25 databases of total size 7TB to a new machine (better hardware).

All settings (logins/dbmail/proxy/credential/lonked servers), and backup/agent job history needs to be retained.

Due to the above, scripting is not an option as it doesn't preserve history.

Maintenance window is of 3 hours.

I have tried setting up new sql server with same version/Patching, followed by restoring the system dbs and user dbs. But this process (including backup/copy/restore takes 7 hours). Without backups (mdf/ldf copy) takes 15 hours.

Alternate approach using full/diff backups:

1. Restore user db with norecovery onto new instance
2. During maintenance window backup/restore Differential backup with recovery
3. Stop original instance
4. Stop new instance and reorganize db files as per the source instance data/log paths
5. Restore master, msdb, model

This approach takes about 1 hour since differentials are small sized. I'm unable to find any msdn documentation for moving sql to new hardware. Is there any risk with this approach? Or any alternate solution?

For example- at step 1 I can restore master db rather than at step 5.

• David Browne - Microsoft: Is your SQL Server running in a VM?
• variable: Yes it is running in a VM
• David Browne - Microsoft: Then why can't you just migrate the VM to the better hardware?
• variable: The OS needs fresh install so that isn't an option

Answer 1

I would suggest to use dbatools.io cmsdlet Start-DbaMigration (https://docs.dbatools.io/Start-DbaMigration) to migrate sever objects like logins, linked servers, etc. It will create a copy of your prod instance without user databases. To migrate databases I would configure db mirroring (yes, it's obsolete but requires no special reconfigurations and works fine) and at the maintenance window would just do the switch of primary and secondary roles. I did the same migrating 800 dbs (40TB+ in size) between continents (Europe-USA) recently. The switch took about 2hrs due to the high amount of online mirrors and weak network channel. To preserve backup/restore history I see two ways:

1. extract/load content of appropriate tables (msdb)
2. restore msdb from the source server (and get Sql agent jobs as a bonus as well as others - dbmail, operators, etc).

• variable: Why not restore master db to get the logins/linked servers/etc?
• Andrey Samykin: Well, it's the fastest way (replace existing system files on the new server with the ones from original server). However, it requires some efforts and knowledge. Usage of DBATools is easy ("fire and forget") and let's you not to worry about technical details. On the moment of the switch there could be new logins, jobs, etc. So, seamless migration from one server to another needs a solution which copies system information and keep user databases in sync with the new server as much as possible.
• variable: Can't find any documentation from Microsoft on migration.