Here are the pre-migration and post-migration steps:
Premigration Steps:
/* 1) run DBCC consistency checker */
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS
/* 1a) Normally we KILL as user connections for an outage but since the application servers were shutdown we didnt need to run SQL script to kill user connections */
/* 2) check for all the logins having access to FEWS database so that there are no permission issues after migration */
/* 3) Backup Database and make sure to click on "verify backup when finished" in options tab */
/* 4) After the backup finishes, copy to destination, restore from the destination */
/* 5) Modified the auto-growth increments of the database for all the secondary files to avoid the DB growing in baby steps and avoid performance issues */
Postmigration Steps:
-- check for orphaned users
exec sp_change_users_login 'report'
-- Some logins are orphaned, so we need to migrate the login from source server
select 'create login ' + quotename(sp.name) + ' with password = ' + CONVERT(nvarchar(max), sl.password_hash, 1) + ' HASHED'
from sys.server_principals sp
inner join
sys.sql_logins sl
on sp.principal_id = sl.principal_id
-- attach the Database user with the server login
exec sp_change_users_login 'UPDATE_ONE', 'sqllogin01', 'sqllogin01'
-- verify that the SQL login sqllogin01logins to the server without access issue.
-- check the port - since NOT listening to dynamic ports it is 1433
select local_tcp_port from sys.dm_exec_connections
where session_id = @@SPID
-- run DBCC on the destination for final confirmation
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS
0 Comments.