Share via

migrate user database to the new SQL Server?

Potter123 260 Reputation points
2023-07-14T05:43:11.7533333+00:00

SQL Server on my computer is the 2014 version and now I plan to upgrade it to the 2019 version.

What I'm asking is: How should I migrate my user database to the new SQL Server?

Thanks in advance!

SQL Server | Other
0 comments No comments

Answer accepted by question author
  1. Anonymous
    2023-07-14T05:47:38.9533333+00:00
    1 person found this answer helpful.
    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator
    2023-07-14T07:56:43.4633333+00:00

    Adding to Olaf's answer:

    For an in-place upgrade you need to consider the OS support. SQL 2019 may not be running on your current OS, and in that case, you may no choice but to move to a new machine.

    Migrating the user databases as such is straightforward, as you can do this with BACKUP/RESTORE. Migrating logins, jobs etc takes some manual work.

    Whichever path you take - in-place or move to a new instance - you should be prepared for performance regressions. They can occur with some bad luck. So do this: once you have SQL 2019 in place, enable Query Store for all your databases, but keep the compatbility level at the current level. Once you have been in production for a while, a week or two, switch to compatibility level 150. The kicker is this: if a query now starts to be slow, you can force the old plan with Query Store.

    In SSMS there is specific support for this scenario. Right-click a database and at the bottom of the context menu, you find Database Upgrade.

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 47,616 Reputation points
    2023-07-14T05:57:56.7733333+00:00

    How should I migrate my user database to the new SQL Server?

    Which upgrade method have you in mind:

    • In-place upgrade: Same machine, only newer SQL Server version? Then do it, the upgrade wizard will do everything
    • Side-by-side? On a new machine? That's much more work with a lot of thinks to migrate/carry over.
    1 person found this answer helpful.
    0 comments No comments

  3. Raksha Chourasia 75 Reputation points
    2023-07-17T06:38:45.35+00:00

    For migrating your database you should follow these steps:

    1. Backup user database
    2. Have installed SQL server 2019 on your destination server and make sure its configured.
    3. Restore the backup to the user database.
    4. Need to test the restored database
    5. Check on the compatibility levels of migrating the database.
    6. Updating connection strings and working on configurations.
    7. Optimising the performance as needed.

    I hope this would help you!!

    0 comments No comments

  4. Potter123 260 Reputation points
    2023-07-17T06:06:38.61+00:00

    Thanks all

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.