We migrated our web server to a newer server. We have the old and new both running while we do testing on the new one. The old one is still being used in production by our clients and internal staff so is more up-to-date than the new one which was current as of a week ago when took a snap shot and did the migration. So I need to migrate the new data on the old server to the new server. What is the best way to accomplish this? If I do a mysqldump and import into the new server, will it overwrite the data already in there? That is fine if so, just concerned about it duplicating records. Basically just want to take everything that is on the old server and dump it into the new one when no one is on the system.
MySQL – How to Migrate Tables to New Server with Existing Tables
migrationMySQL
Related Solutions
Do not use copy database wizard. Instead use backup/restore. Also, if you want to minimize the downtime then below steps would be sufficient :
Prepwork on the New server :
Create blank databases with the same name as the old server. This is because if any logins or jobs has that database as default, there would be errors.
Script out all the logins using the
sp_help_revloginfrom the old server to the new server and create them on the new server.Script out all the jobs. You dont have to Script > CREATE To for each jobs. Instead right click in Object Explorer and script all the jobs in one shot :-) Remember to disable the jobs until you failover to the new server.
Scrip out all Linked servers and recreate them on the destination server. Remember that passwords will not be scripted out.
Once 1-4 is set up then set up either Logshipping or database mirroring (Async mode) from source server to destination server.
On the day of failover
if using logshipping then manually failover to new server.
if using Mirroring then change the mode to synchronous and then initiate a failover to new server.
This way you will do all the prep work before hand and then during the day of failover, the downtime will be minimal.
You can refer to my answer HERE for more steps (they are for migration from old version to higher version, but still will help you).
Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time:
1) Backup and Restore Databases - Is this the best option?
It's an option. If you need the downtime to be less, you can use mirroring or log shipping (or do it by hand) to keep the databases in sync and then migrate during a fast downtime or cutover.
Please note that system databases cannot be log shipped or mirrored and would need to either be frozen or copied right before the cutover.
2) Migrate Logins - use Microsoft KB? kb/918992
If you restore the master database to the new instance, no migration should be needed. All server level logins are stored in the master database.
3) Migrate Credentials/Certificates - what is required for this?
If this is inside of SQL Server, these would be held in their respective databases (and possibly master as well). The one difference would be the service master key (SMK) that would change. You make want to back that up and restore it on the new server if you're relying on automatic key decryption.
4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)
If you restore the msdb system database, all agent jobs will be held in it. There would be no need to script->restore.
5) Migrate SSIS Packages - (How to do this?)
If the SSIS packages are on the filesystem (not default) it would be trivial to create the same location on the new server and copy. By default the SSIS packages are held in the msdb system database and restoring it would get you to the same place, just like #4 and previous.
6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)
This is also stored in msdb. See #4, #5.
7) Recreate assemblies
These live in their respective databases. If backup and restore is used, this should not be a problem. Any assemblies outside of SQL Server would need copied to the new server.
8) Recreate Linked Servers
These also live in the master database, see #2.
Related Question
- SQL Server – Migrating SQL Database to a New Server
- SQL Server Migration – Server Name in Stored Procedures References Old Server
- Sql-server – Migrate on-premise SQL Server databases to Azure SQL Database
- Mysql – Migration of MariaDB/MySQL to another server without downtime
- Mysql – Migrate legacy application to new db/schema
- Sql-server – Migrate MySQL to MSSQL with views
- MYSQL Huge Data migration (5TB) from old server to new server
Best Answer
When dumping, use the option to not write the
CREATE DATABASEstatement.When reloading, specify a new, empty, database on the
mysqlcommand.That will put the imported data in a different database. You can then compare them, or do whatever. However, if you plan to move them into the old db name, you would have to do
DROP TABLE+RENAME TABLEone table at a time to move them into place.Alternatively, you could move the old data out of the way (drop + rename), then load into place.