Migrate database from SQL Server 2005 to SQL Server 2019

The following steps are used to migrate a SQL Server 2005 database (Northwind2005) to a SQL Server 2019 instance (Northwind2019):

Step 1 – Setup the SQL Server 2019 instance
You may already have an existing 2019 instance available. If you are setting up a new instance, please have a look at my list of default configurations that should be made. Also, install the latest patches.

Step 2 – Restore last Full backup WITH NORECOVERY
Whether you take nightly full backups or weekly full backups plus nightly differentials, restore the backups of the 2005 database into the 2019 instance.
The restore should be done using the WITH NORECOVERY option to prevent any users from connecting to the server and querying the database. This restore should be done prior to the cutover time to minimise downtime.

Step 3 – Set database compatibility level to 100
The lowest supported compatibility level in SQL Server 2019 is 100.
The highest supported compatibility level in SQL Server 2005 is 90.
The compatibility level of the 2019 database should be set to as close to the 2005 compatibility level as possible. Some kind of regression testing should be done to ensure no T-SQL code or query breaks because of differences between the level 90 and 100 compatibility.

Step 4 – Generate Logins
Connect to the SQL Server 2005 instance and create a stored procedure to generate logins on the SQL Server 2005 database.
Execute the procedure and copy the results:

EXEC sp_help_revlogin

The results text will be run in the SQL Server 2019 instance to create the logins for the 2005 database.

Step 5 – Copy any scripts from SQL Server 2005 instance
The script are usually diagnostic tools found in master database. Evaluate whether these scripts should be migrated as well or need to be updated to the latest versions.

Step 6 – Check for any currently running jobs
Find out if there are running jobs. As long as there are no running jobs, you are cleared to begin the migration.

-- find jobs that are currently executing
SELECT sj.name, sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj 
ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
   AND sja.stop_execution_date IS NULL;

If there are no running jobs, find out if there are jobs about to start within the next hour. These jobs will be run on the 2019 server when the database is migrated:

-- find jobs that are going to execute within the next hour 
select sj.name, sja.*
from msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj 
ON sja.job_id = sj.job_id
where next_scheduled_run_date >= getdate()
and next_scheduled_run_date <= dateadd(HOUR,1, getdate());

Step 7 – Script all agent jobs
SSMS => SQL Server Agent => Jobs => select all jobs and right-click => Script Jobs as => CREATE to => New Query Editor Window
Evaluate each of these jobs and decide if they will be recreated in the 2019 instance.

Step 8 – Evaluate Windows Tasks Scheduler jobs
Investigate any Windows Tasks Scheduler jobs that either call database procedures through SQLCMD or run independently of the database. Copy the scripts and the scheduled times. Decide if the tasks will be recreated on the machine with the SQL Server 2019 instance. You can use this table to keep track of the tasks:

Task Name	Time	Frequency	Scripts
ETL Job	8:00 pm	Daily	C:\scripts\etl_job.bat

Step 9 – Take Production SQL Server 2005 database offline
Now the Cutover begins. End users would have been notified of the time the application would be unavailable. To take the system offline, you can simply stop application service and set the database to single user mode:

ALTER DATABASE Northwind2005
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Step 10 – Take differential backup of SQL Server 2005 database
Because the new instance would already have the latest full backup, the differential backup would only contain data changed from the last backup which should have been from the previous night.

Step 11 – Restore diff backup on 2019 database with Recovery
Set the migrated database to use multi_user mode:

ALTER DATABASE Northwind2019
SET MULTI_USER
WITH ROLLBACK IMMEDIATE;
GO

Now the database should be in a state to put in production. Before you do, don’t skip the next step.

Step 12 – Verify data is current
Verify that the differential backup has been restored by querying the latest data that would have been restored with the differential backup.

Step 12 – Point application to new instance
Start application server service which should have been configured to point to the new database. The application owner will then be informed that they can resume using the server.

Step 13 – Execute agent jobs that would have been missed during the migration process
You would have found these jobs in Step 6.

Step 14 – Setup backup job for the migrated database
If the backup job was not one of the agent or task scheduler jobs, you can set it up now.

Step 15 – Stop the database service for the SQL Server 2005 instance
You may not be able to do this as there may be other databases to be migrated. If this is the case, leave the database in single_user mode.

Note: This is one method of doing a migration. You can also do a cold backup of the SQL Server 2005 database and attach the datafiles to the SQL Server 2019 instance. Or you can log ship up to the 2019 database.