How to migrate SQL server 2008 to 2019 by using SSMS

Some of our legacy applications’ database hosting on a 2008 SQL server. We need to migrate the schema and data to SQL server 2019.

Microsoft’s official solution is to use the DMA tool to do that, but Denise found we could also use SSMS to do so. In this document, we will show how to do that.

To migrate the schema of the database, we could use the ‘generate script’ function. Right-click the database we want to migrate and then go to ‘Task’, ‘Generate Script’.

Then follow the guide to select the objects you want to migrate and save the script to your computer. This step may take some time please wait until the program finishes running.

And then run the generated script in the target SQL server. Some creating users operations may cause an error, we could resolve it by removing all the creating user sentences.

After the script finished running, we could start to migrate data. The first step is to connect to both the source and target databases at the same time.

And then use the export data function. Right-click the source database go to ‘Task’ then ‘export data’.

Then follow the Wizard, chose the correct data source/target as ‘SQL server Native Client’ and input the source and target server name then chose the corresponding database. Chose ‘copy data from one or more tables or views.

Chose the source tables with the same schema name and then click the ‘Edit Mappings’ button. In the pop-up window check ‘Enable identity insert’(to insert the column of primary key).

And for some columns like ‘Last Modify’ and ‘Created at’, those columns only allow insert the timestamp so we need to ignore those columns during the migration or it will give us an error.

After migrating all those tables’ data we finish the migration.