Migrate MSSQL user databases.

Question

Hi,

I am about to migrate 1,000 databases from several older SQL installations (2008-2019) to a new SQL Server 2022 environment.

Is it recommended by Microsoft to migrate databases by using either backup and restore (log shipping for larger databases) OR detach --> copy files --> attach database?

Please recommend best practices for a production environment migration, and why if possible.

Answer 1

The safest way way is to backup & restore; never use detach/attach, worst case is the database files get damaged in some way.

For the rest of your post: You provided to less information to give a meanfull answer.

Answer 2

I concur with Olaf: Use BACKUP/RESTORE. And log ship for larger databases where you cannot accept a longer downtime.

As for best practices, are all these databases of the same schema? That is, do you have a multi-tenant solution with one database per customer? Or are these 1000 databases all part of different applications?

Good practice is to conduct some level of testing before you migrate, and if this is a multi-tenant solution, I guess you only need to test one or two. If they are 1000 different applications, there is a ton of work for you and your organisation...

Answer 3

Hi @Morten Brennebjerg Civitarese Olsen,

Use back up files and then restore maybe the easiest way.

Please try in your test environment first and then apply to your production environment.

You may also use SSIS to transfer the databases.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.