4

I'm upgrading an internal database at work, and we are creating a new VM to host it.

I can't see an option to generate a script to export the SQL Server 2012 SP1 database for server version SQL Server 2019. Would it be better for me to:

  • Follow the upgrade path for SQL Server 2012 to SQL Server 2019 on the current database server, and export the database to the new server when the upgrade is complete.

or

  • Export the database from SQL Server 2012 as is, and script it for the highest server version available (2017).

If I installed SQL Server 2012 SP4, would that give me an option to Script for Server Version SQL Server 2019?

Thanks for your help in advance.

Improve this question
2
  • 1
    What's the size of the involved DB?
    – Paolo
    Jan 11, 2020 at 7:46
  • Small, only 2.5 GB
    – Bingobango
    Jan 13, 2020 at 13:35

2 Answers 2

Reset to default
11

This handy page from Microsoft should be very useful for you as it explains in detail how to carry out a migration and the tools and methods available: Supported Version and Edition Upgrades for SQL Server 2019

Use the Data Migration Assistant (DMA) to scan your databases to make sure they can be migrated to the 2019 server. It will highlight any potential blocking issues such as deprecated features that you may need to resolve before you can migrate.

It looks like you will still need to upgrade your SQL Server 2012 to SP4 to be fully supported though.

Improve this answer
3
  • It looks like I will have to apply the SP4 service pack alright. Is the DMA only for migrating databases to Azure or can it be used for on-premises versions of SQL Server too?
    – Bingobango
    Jan 10, 2020 at 11:19
  • 1
    The DMA can be used for on-premise migrations. From the linked documentation: Migrate an on-premises SQL Server instance to a modern SQL Server instance hosted on-premises.
    – John K. N.
    Jan 10, 2020 at 11:30
  • I chose this answer as the DMA tool has found all of the procedures that will fail the migration.
    – Bingobango
    Jan 10, 2020 at 15:11
9

We normally just ...

  1. Perform a simple SQL Server Database Backup on the old SQL Server instance (or with Transact-SQL command BACKUP)
  2. Copy the database backup (*.BAK file) to the new SQL Server instance
  3. Restore the database using either SQL Server Management Studio (SSMS) or using the Transact-SQL command RESTORE DATABASE ....

During the restore of the database on the new instance it is automatically converted into the newer database version.

If you restore a SQL Server 2005 (9.x) or higher database to SQL Server 2019 (15.x), the database is automatically upgraded. Typically, the database becomes available immediately. However, if a SQL Server 2005 (9.x) database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. If the upgrade option is set to import (upgrade_option = 2) or rebuild (upgrade_option = 0), the full-text indexes will be unavailable during the upgrade. Depending the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. Note also that when the upgrade option is set to import, the associated full-text indexes are rebuilt if a full-text catalog is not available. To change the setting of the upgrade_option server property, use sp_fulltext_service.

Reference: Restore a Database to a New Location (SQL Server) (Microsoft | SQL Docs)

Important

Don't forget to create scripts for the SQL Server Logins, which can then be used to re-create them on the new instance. Visit the article How to transfer logins and passwords between instances of SQL Server (Microsoft Support) to see how this can be achieved.

Improve this answer
2
  • Does the import fail if compatibility issues are found when the backup is restored on a newer SQL version?
    – Bingobango
    Jan 10, 2020 at 11:18
  • 1
    I have personally rarely encountered a database that was using a deprecated feature that wouldn't be supported in the newer version, except for once when I had a full text index that couldn't be recreated. If I recall correctly there was a hint in the restore summary regarding the full text issue.
    – John K. N.
    Jan 10, 2020 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.