MSSQL 2012 -> MSSQL 2019 -> MSSQL 2012 - Older version of Database

  • Hi,

    I migrated a Database from MSSQL 2012 to MSSQL 2019 and that's supported.

    But, now i got this ingenious idea of migrating back from MSSQL 2019 to MSSQL 2012 since i made some changes in the database.

    SQL 2012 Version: Microsoft SQL Server Standard (64-bit) Version 11.0.7507.2

    SQL 2019 Version: Microsoft SQL Server Enterprice(64-bit) Version: 15.0.2000.5

    Database Version:

    Collation: Finnish_Swedish_CI_AS

    Recovery Model: Full

    Compatible Level: SQL Server 2021 (110)

    Containment Type:None

    Database Size: 4GB

    Logs: 500mb

    How:

    1. Scripting

    I see there are some scripts out there but it feels like a minefield, is this a bad path maybe ?

    But only works with <1GB file size.

    https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/

    2. Tools

    https://www.systoolsgroup.com/sql-server/migration/

    https://www.apexsql.com/sql-tools-diff.aspx

    3. Stay on 2012 - redo the correct way

    Setup a new 2012 SQL server and import the old DB there, execute the changes and then import back to the old 2012 instance maybe. And does it have to be exacly the right version (11.0.7507.2) or is any version of 2012 fine?

    Conclusion:

    What is your recommendation and is there any free tools or scripts that can handle a 4GB db? SSIS  maybe ?

    Thanks

    • This topic was modified 2 months ago by  HA4g3n.
  • Going backwards is hard. There are no shortcuts. The best way is going to be to script everything out and then build data migration processes using whatever tools you're most familiar with. You could try creating a bacpac from the 2019 database. Assuming you haven't created any objects that exist in 2019 but not 2012, I think that will work. Testing is your friend here.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply