Skip to content

Migrating SQL Server database

Here we will talk about detailed steps on migrating SQL Server database. Before we start the migration task, we need to complete the below checklist.

Checklist:-

1) Are we migrating to same SQL Server version on different server?

2) Target database name will be same or different from the source database name?

3) Available free space in target server?

4) If we are migrating (upgrading) to higher SQL Server version then can we change the database compatibility level to newer version? (If we don’t change the compatibility level to the new version, then there is no point in upgrading to higher version, as we will not be able to use the features of newer version)

5) Do we have shared path that we can use to copy the backup files from source server to destination server?

6) Once migration is completed, do we have to bring the source database offline immediately?

7) Is there any SQL Server Agent job that depends on the migrated database and need to be migrated? Querying sysjobs table in msdb database can give this information. (Generally, application team confirms if they need the job to be migrated to new server or not)

Detailed steps to start with migration activity: –

NOTE: – Here we are migrating database ‘TEST123’ from server-A (SQL Server 2005) to server-B (SQL Server 2008). Database will be named as ‘TEST321’ in destination server.

1) Confirm with application team if they have stopped all the application specific services.

2) Check if there is any active connection to the database TEST123 by running:

sp_who2 and then look for the connections to the TEST123 database

or

select spid, last_batch, status, hostname, program_name, loginame,

st.text as SQL from sysprocesses SP 

CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST

where SP.dbid = <dbid_of_database_TEST123> 

If you find any active connection, talk to application team and wait for the active connections to close or Kill the connection by running: –

Kill spid

3) Take the database to read only mode

alter database TEST123 set read_only;

4) Take a full backup of TEST123 database by running below command or from SSMS

backup database TEST123 at disk=’full_path\TEST123.bak’ with stats;

5) Copy the above backup files to new server. Incase you don’t have box level access then you can copy by running in query analyzer:-

use master

go

xp_cmdshell “copy source_path destination_path”

go

6) Now we need to know the logical data and log file name of TEST123 database, which is required to restore the database in new server. Run below command and copy the logical data and log file name of the database: –

sp_helpdb TEST123

Say, the logical name of data file is TEST123_Data and logical name for log file is TEST123_Log

7) Run the below command in destination server to restore the database.

Restore database TEST321 from disk=’Full backup path\TEST123.bak’ with

move ‘TEST123_Data’ to ‘Fullpath\TEST123_DataS.mdf’,

move ‘TEST123_Log’ to ‘Fullpath\TEST123_Log.ldf’, 

stats;

8) Change database owner to ‘sa’

USE TEST321

go

sp_changedbowner ‘sa’

go

9) Now check for any orphan users

USE TEST321

GO

sp_change_users_login ‘Report’

GO

If any orphan user is found, resolve the issue by running below command for each orphan user

USE TEST321

GO

sp_change_users_login (‘update_one’,’orphan_user’,’login_name’)

Go

10) Run dbcc checkdb on TEST321 database to check for any consistency or allocation errors.

11) If we are migrating newer version of SQL Server then we need to run DBCC UpdateUsage (TEST321) to report and correct pages and row count inaccuracies in the system catalog views.

12) If we are migrating to newer version of SQL Server and application team have confirmed that we can change the compatibility level to current version then do the same by running below command: –

EXEC sp_dbcmptlevel ‘TEST321’, 100 

13) Create maintenance plan jobs to take TEST321 database backup.

14) Script out the database related in source server (if any) and create the same in destination server.

15) As per confirmation with application team, take the source database TEST123 offline. Also, remove this database entry from the backup and maintenance plan jobs.