How to Transfer Database from Sql Server 2012 to Sql Server 2017


Problem:

    I needed to transfer the database and the schema (Database objects as well as the data) from SQL Server 2012 to 2017. Every time I would use other website documentation, an error would happen that consumed a lot of time to resolve. Below, is the solution that helped solve the problem of migrating data from one server to another.

Solution: 

1. Go to SQL Server 2012 and right-click on the Database > Tasks > Script the database ( as a schema),
    - On the Script-Database window, go to the advanced button and set your settings from here, e.g. if you want the script to contain "Using Database Name" on top of the SQL script, you can set that in the advanced. If you include "Using Database Name" in the script, make sure that you create a Database on the other SQL Server you want to transfer the database to.

2. After Scripting your schema, change the database to point to the newly created database you want to transfer the schema (Database Objects) to. This can be done if you are using Microsoft SQL Server Management to connect to the old SQL server as well as the New Server. (If you are wondering how to connect to many instances using one MSSM, just click on the small icon that looks like a computer and you will be able to connect to another Server provided credentials)

3. After completing step 2. Run the script against a new SQL Server with an appropriate Database selected as the target. Check and validate all the tables and Schema (Most importantly Primary Keys and Foreign Keys has been created). After validating all database objects that have been transferred/created on the new SQL Server's Database, go back to SQL Server 2012 (This can be done by clicking on the SQL Server 2012 Server within the same"Object Explorer" and right-clicking on the database > Tasks > Export. Then the Export Data wizard will appear and then pick it from there.

Conclusion:
    The documentation above is just one way to help you transfer the Database Schema from One Server to another. Before trying the method above, I went through several other methods including using SSIS packages to transfer Database objects but nothing worked for me. If you want to transfer one database object to another server then other ways to database migration might work, but if the database object is too large or transferring from SQL 2012 to 2017 (There have been many Database upgrades) then this way of migration might work for you.

[NB] When transferring a database using this method, look out for data overridden, e.g. you will need to make sure the data integrity is kept in place. For Example, if a table includes a Date Column indicating when the record was inserted then make sure to not override that column. This becomes hard to achieve if a large database is being migrated.


SQL
published
v.0.01




For peering opportunity Autonomouse System Number: AS401345 Custom Software Development at ErnesTech Email AddressContact: [email protected]