SQL Server Backup & Restore Without Affecting Users or Logins

Question

The scenario: I am retiring a production SQL Server (SQL Server 2008) and relocating to another SQL Server (ver 2019) on a different computer. I want to do it in phases:

Steps/Phases:

1. Backup a database on current server and restore onto new server, leaving current database on-line

2. Add and delete logins, and users. This may take several days while developers test their applications' connections to the new server.

3. Update the database on the new server with data only, not affecting new logins/users

4. Shut down old database

Step 3 is where I need help, not sure how to refresh the data only, without affecting logins and users.

It looks like Log Shipping might be a valid technique but there seems to be an issue: the Primary DB has Recovery Model set to Simple.

Questions:

What will happen if Recovery Model is changed?

1. Does the server have to be restarted?

2. Will performance be affected?

3. Will it use considerably more disk space?

4. Can Log Shipping be used from SQL Server 2008 to SQL Server 2019

Background: I tried the Database Backup Wizard but it doesn't really satisfy my requirements.

TIA

Answer 1

Add and delete logins, and users.

Do not mix up logins (server principals usually stored in master database) and users (database principals stored in target database and transferred after database is restored or log shipped)

You have to create logins.

Step 3 is where I need help, not sure how to refresh the data only, without affecting logins and users.

You can setup transaction replication or custom made ETL process.

It looks like Log Shipping might be a valid technique but there seems to be an issue: the Primary DB has Recovery Model set to Simple.

You still can use log shipping approach with restoring differential backups.

What will happen if Recovery Model is changed?

1.Does the server have to be restarted?

No

2. Will performance be affected?

No

3. Will it use considerably more disk space?

You must run log backup to avoid log file growing. You must manage space for log backups.

4. Can Log Shipping be used from SQL Server 2008 to SQL Server 2019

Yes

Beware, in log shipping the target database is opened in read-only mode.

Here are my steps when I did migration.

1. restore the database on new server and open it in read write mode to test.
2. before migration backup, copy and restore full backup in norecovery mode,
3. on the cut over day, you backup, copy and restore differential backup (smaller in size, low downtime)

Check for other items not covered by log shipping: objects in master database, SQL agent jobs, Database mail configuration etc.