SQL Server Database Migration Checklist

As SQL DBA, you might come across activity like SQL Server database migration from one to another server. While migration of database you should take care some important things like transfer of logins or dependent objects and jobs related to database that will participate in migration. There is possible chance of missing some steps which hurt SQL Server performance later now. That’s why before going for such activity, you should prepare pre-checklist that will tell you how much you have completed and what steps you have to follow.

Here are the check list for SQL Server database migration with script

It will help to collect database related information
1. Check whether you have enough space on target Server for the migrated database. you can find out by executing below extended stored procedure.
exec master..xp_fixeddrives

2. Check the database property that will you to compile features after restoration of database on target server. Below script will help to collect database properties
SELECT
DB.database_id,
DB.Name as ‘DB Name’,
Login.Name as ‘DB Owner’,
DB.state_desc,
DB.recovery_model_desc,
DB.collation_name,
DB.user_access_desc,
DB.compatibility_level,
DB.is_read_only,
DB.is_auto_close_on,
DB.is_auto_shrink_on,
DB.is_auto_create_stats_on,
DB.is_auto_update_stats_on,
DB.is_fulltext_enabled,
DB.is_trustworthy_on
FROM sys.databases DB
INNER JOIN sys.Logins Login
ON DB.owner_sid = Login.sid
3. Find out the list of sql server jobs that will impact after database migration.

SELECT distinct name, database_name from msdb..sysjobs sj
INNER JOIN msdb..sysjobsteps sjt on sj.job_id = sjt.job_id
4. Check the dependent objects like linked Server whichh can be find out by running below query.
select * from sys.servers
After completing above checklist now you can start database migration

Step By Step SQL Server Database Migration

1. Check whether users are currently connect to database or not by running sp_who2 command. If you see any user in the list. Inform to the user and disconnect the session.
2. Now stop application services on primary server.
3. Take full database backup of all databases that you want to migrate.

Backup database db1
To Disk = ‘D:\db1.bak’
4. Move all backup files on target server to restore.
5. Now restore all backup on target server using below script or SSMS (SQL Server Management Studio 2005)

Restore database db1
From Disk = ‘D:\db1.bak’
With Recovery
6. Change the database owner
sp_changedbowner ‘sa’–> would be change to any login
7. Set compatibility mode for the database (optional) if your moving on same version of sql server.

ALTER DATABASE DBName
SET SINGLE_USER

EXEC sp_dbcmptlevel DBName, 90;

ALTER DATABASE DBName
SET MULTI_USER
8. Transfer login from primary server to target server by executing below script that are provided by microsoft, copy the output and execute that on target server.

USE MASTER

DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
declare @login_name sysname

set @login_name = null

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin
FROM
sys.server_principals p LEFT JOIN sys.sysLogins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin
FROM
sys.server_principals p LEFT JOIN sys.Logins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs

END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr
IF (@type IN ( ‘G’, ‘U’))
BEGIN — NT authenticated account/group

SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS
WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
END
ELSE BEGIN — SQL Server authentication
— obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )

–EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
DECLARE @charvalue varchar (514)
declare @hexvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@PWD_varbinary)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@PWD_varbinary,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END

SELECT @PWD_string = @charvalue

— Password
–EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@SID_varbinary)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
— DECLARE @tempint int
— DECLARE @firstint int
— DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@SID_varbinary,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END

SELECT @SID_string = @charvalue

— obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked
WHEN 1 THEN ‘ON’
WHEN 0 THEN ‘OFF’
ELSE NULL END
FROM sys.sql_logins WHERE name = @name

SELECT @is_expiration_checked = CASE is_expiration_checked
WHEN 1 THEN ‘ON’
WHEN 0 THEN ‘OFF’
ELSE NULL END
FROM sys.sql_logins WHERE name = @name

SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name )
+ ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string
+ ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN — login is denied access
SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN — login exists but does not have access
SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN — login is disabled
SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs

 

9. Check the list of Orphan Login
sp_change_users_login ‘report’
10. Fix Orphan User by mapping user with there respective login

sp_change_users_login ‘update_one’,’admin’,’sysadmin’
To save your time, execute the below script for automatically fixing of orphan users.

DECLARE @username varchar(25)
DECLARE fixOrphanusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixOrphanusers
FETCH NEXT FROM fixOrphanusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login ‘update_one’, @username, @username
FETCH NEXT FROM fixOrphanusers
INTO @username
END
CLOSE fixOrphanusers
DEALLOCATE fixOrphanusers

Now after successful migration of sql server database ask application team to check the functionality with users.

Leave a Reply

  

  

  


− 1 = 2