DATABASE MIGRATION

Lower version of Microsoft SQL Server to Higher version of SQL Server.

                                      A complete DBA Guide with Automated Scripts and Steps

Introduction:

I have seen lot of DBA’s  spending much of their time in making migration plans and even while implementing such changes in their customer environment. As Microsoft is releasing newer versions of SQL Servers frequently, based on business and customer requirements, in the same phase, application services has to be migrated  to meet-up customer requirement and evade End of Life risks. Still many clients are using SQL Server 2000 (n 2005) versions in their environment, which have to be upgraded in coming days. Here I will be providing the easiest solution for migrating a Lower version of SQL instance databases to a higher version of SQL Instance in faster way with minimum downtime.

There are many ways and methods to migrate these legacy databases, few consider backup and Restore strategy for moving  databases from one SQL instance to another, but it’s a time consuming process, and it works well when we have few databases in the migration scope. What if we have to migrate 50+ or 100+ user databases from one SQL instance to another SQL instance, well, the best possible method and solutions are outlined over here to ease the administrator activity.

To minimize the application downtime, we should plan well in advance with all the pre-requisites and complete implementation and rollback scripts ready for executing during this activity. Try to avoid using GUI to make any configuration changes for an instance or to a database. Well, without any further enlightenment, let's get into the steps - I will be providing more notes and comments in each section as we go....

Assume that we are migrating databases from SQL Server 2005 to 2012\ 2014; the steps will remain same though you migrate databases from SQL 2000 to 2008 or R2.

Please read carefully and follow each step -

Pre-Migration checklist:

1.    Make sure destination server has enough disk space for copying all database files.

2.    Make sure required ports are opened for the application server.

3.    Make sure that SQL Server collation is either same or as per the application or vendor requirement.

4.    If any of the database is using Fulltext Seach feature, refer - additional references at end of this article.

5.    DTS Package migrations  are not covered here, refer - additional references at the end of this article.

6.    Only single LDF file is considered for attaching  script on destination server, in-case you have multiple log files, either remove it  or manually include it in the output script. Multiple NDF's are considered without any issues in all the scripts.

7.    You should read all the notes from each section (and scripts for updating few parameters).

8.    Update the parameters ( destination server name,  share folder, compatibility level etc) as applicable for your environment and requirement in the migration activity.

Important Note: Scripts 1 to 8 (and STEP2) should be executed well in advance and saved, and If requires make necessary changes in the scripts and query output as per your environment and requirement.

Table 1:

  *Scripts used for rollback as well.              **Well in advance.              ***During actual change.

STEP 1:

1.  Take SQL instance and database configuration details from the source server and save the results in an excel file for reference during migration activity.

Sheet 1 -  

                 sp_helpdb

Sheet 2 -  

                 select  * from master.sys.master_files

Sheet 3 -  

                 select * from master.sys.configurations -- To run on SQL 2005 and above.

                 sp_configure -- To run on SQL 2000 (enable show advanced options and rerun).

Sheet 4 -

                 SELECT * FROM master.sys.sysfulltextcatalogs

STEP 2:

Script out all logins, jobs, linked servers, operators, any user procedures on system databases, SQLMail or Database mail etc.

For scripting out logins - follow the below links.  

As per the links, create "sp_help_revlogin" procedures on the source server well in advance, and copy the scripted logins.  

Note: Remove system accounts and copy only the required logins to the destination server.

http://support.microsoft.com/kb/918992

http://support.microsoft.com/kb/246133

For scripting out jobs and other objects -

Identify what jobs and other objects should be migrated to the destination SQL Server. To do this, launch SQL Server Management Studio - Go to SQL Server Agent, select Jobs folder -> Click on Object Explorer Details from View Menu. holding ctrl key, select required jobs from the right pane -> After the selection Right click ->Script Job as -> Create To -> New Query Editor Window  or save to a File. Similarly most other objects (such as operators, linked servers, Alerts, etc) can be scripted out to a New Query Window or to a SQL File. Finally the scripted objects should be executed on the destination server using SSMS. 

STEP 3:

The given t-sql scripts [# 1 to # 8] must be executed on source server to generate the implementation scripts well in advance(few days or weeks before), and the same query output (t-sql commands) will be executed on the destination server (and on source server) at the time of actual implementation period.

/*============================================================
Script 1 - Generate script for Detaching databases.
Important Notes:
 
Run the below Query on Source server, to generate the detach commands in the query result window.
Saved query output (t-sql) commands must  be executed on the Source server during your actual change window.
The same sql commands can be used for detaching the database on destination server incase rollback is required.
 
Add the parameter @keepfulltextindexfile='true' incase any of user db is having fulltext seach index catalog. This parameter will be removed next releases.  
 
Detach command wont work in the below scenarios:
 
If the database is used in replication, it must be unpublish.
Before you can detach the database, you must drop all of its snapshots.  
If the database is used in mirroring session, it must be dropped.
A suspect database cannot be detached; before you can detach it, you must put it into emergency mode.
 
 Author : Dathuraj Pasarge
Last Edited : 2nd June 2014
===============================================================*/
declare @dbname varchar(150)
declare @cmd varchar(250)
 
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
 DEALLOCATE dbcursor
 
END
 
declare dbcursor cursor for
select name from master..sysdatabases where dbid>4 and name not in('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')  -- Include any additional databases in NOT IN clause to exclude the databases from detach.
 
Open dbcursor
Fetch next from dbcursor into @dbname
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
        Begin
        set @cmd='sp_detach_db '+''''+@dbname+'' +''',''true'''
        
        print @cmd
        print 'go'
        End
 
        Fetch next from dbcursor into @dbname
END
 
close dbcursor
deallocate dbcursor
 

Output

/*===============================================================
Script 2 : Generate script - Re-Attaching the databases on source server,
 incase of rollback.
 
Important Notes
----------------------
Run it on the source server, and save the query output for reattaching the databases,  
incase of rollback is required for any unseen issues occured during  
the migration activity.
 
Note: Scripts for Rolling back the change.
 
Author : Dathuraj Pasarge.
Last Edited : 2nd June 2014
================================================================*/declare @dbname nvarchar(450)
declare @cmd nvarchar(1200)
declare @mdf nvarchar(800)
declare @ldf nvarchar(800)
 
DECLARE @serverVersion varchar(50)
select @serverVersion = CONVERT(varchar(50),SERVERPROPERTY('ProductVersion'))
SET @serverVersion = LEFT(@serverVersion, CHARINDEX('.', @serverVersion) - 1)
 
 
 
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
 DEALLOCATE dbcursor
 
END
 
If  convert(int,@serverVersion)<9
    
BEGIN
declare dbcursor cursor for
 
select name from master..sysdatabases where dbid>4 and name not in('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')
-- Include the db's in NOT IN clause, which are suspect or inaccessible.
 
Open dbcursor
 
Fetch next from dbcursor into @dbname
 
WHILE @@FETCH_STATUS = 0
BEGIN
    Begin
set @mdf = RTRIM((select [filename] from master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=1))
set @ldf= RTRIM((select [filename] from master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=2))
 
set @cmd='sp_attach_db '+''''+@dbname+''','+ ''''+@mdf+''','''+@ldf+''''
 
    print @cmd
    print 'go'
    End
 
Fetch next from dbcursor into @dbname
END
 
close dbcursor
deallocate dbcursor
        END
    ELSE  
If  convert(int,@serverVersion)>=9    
BEGIN
    declare dbcursor cursor for
 
select name from master..sysdatabases where dbid>4 and name not in('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')
-- Include the db's in NOT IN clause, which are suspect or inaccessible.
 
Open dbcursor
 
Fetch next from dbcursor into @dbname
 
WHILE @@FETCH_STATUS = 0
BEGIN
    Begin
 
set @mdf = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=1
and type_desc='ROWS'
)
set @ldf = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=2
and type_desc='LOG')
 
set @cmd='sp_attach_db '+''''+@dbname+''','+ ''''+@mdf+''','''+@ldf+''''
 
print @cmd
    print 'go'
    End
 
        Fetch next from dbcursor into @dbname
    END
close dbcursor
deallocate dbcursor
END

Output

/*==========================================================
Script 3 : Generate XCOPY commands  
 
Important Notes :
Where to run - On source server.
The below script generate the XCOPY commands for all the data file( mdf and ndf) and log file (ldf) files for  copying from Source Server to Destination server.
 
Update the destination server name for the @DestinationServerName variable and similarly update the 2 share folders that were created on the destination server. Share folders hsould have full control for the user account copying these files.
 
Author : Dathuraj Pasarge
Last Edited : 10th Dec 2014
==============================================================*/
 
declare @dbname nvarchar(150)
declare @cmd NVARCHAR(2000)
declare @MDFSourceFile nvarchar(500)
declare @LDFSourceFile varchar(500)
declare @NDFSourceFile nvarchar(500)
declare @MDFdestinationPath nvarchar(800)
declare @LDFdestinationPath nvarchar(800)
declare @DestinationServerName nvarchar(200)
declare @DestinationShareFolderMDF nvarchar(200)
declare @DestinationShareFolderLDF nvarchar(200)
declare @count1 int, @count2 int, @fileid int
 
set @DestinationServerName='SQL02' -- Update destination serverName
set @DestinationShareFolderMDF='Share1' -- Update ShareFolder created on destination server, for copying all mdf files
set @DestinationShareFolderLDF='Share2' -- Update ShareFolder created on destination server, for copying all ldf files
 
set @MDFdestinationPath='\\'+@DestinationServerName+'\'+@DestinationShareFolderMDF+'\MSSQL\DATA'  -- update folder names as per your requirement
 
set @LDFdestinationPath='\\'+@DestinationServerName+'\'+@DestinationShareFolderLDF+'\MSSQL\Logs'  -- update folder names as per your requirement
 
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
 DEALLOCATE dbcursor
 END
 
DECLARE @serverVersion varchar(50)
select @serverVersion = CONVERT(varchar(50),SERVERPROPERTY('ProductVersion'))
SET @serverVersion = LEFT(@serverVersion, CHARINDEX('.', @serverVersion) - 1)
 
If  convert(int,@serverVersion)>=9
BEGIN
declare dbcursor cursor for
select name from master..sysdatabases where dbid>4 and name not in('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')  
-- Include the db's in NOT IN clause, which are suspect or inaccessible.
 
Open dbcursor
Fetch next from dbcursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
 
    set @MDFSourceFile = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=1)    -- For MDF Files
    set @cmd='XCOPY '+'"'+@MDFSourceFile+ '" "' +@MDFdestinationPath+ '"'
    PRINT @cmd
 
    set @LDFSourceFile = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=2) -- For LDF Files
    set @cmd='XCOPY '+'"'+@LDFSourceFile+ '" "' +@LDFdestinationPath+ '"'
    PRINT @cmd
 
    set @count1 = (select count(physical_name) from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id>2)
    set @count2=1
    set @fileid=3
    WHILE (@count2<=@count1)
    BEGIN
                set @NDFSourceFile = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=''+@fileid+'' )  
                set @cmd='XCOPY '+'"'+@NDFSourceFile+ '" "' +@MDFdestinationPath+ '"'    
                set @count2=@count2+1
                set @fileid=@fileid+1
                PRINT @cmd                            
    END
    Fetch next from dbcursor into @dbname
    PRINT ''
    END
 
END
 
If  convert(int,@serverVersion)<9
BEGIN
declare dbcursor cursor for
select name from master..sysdatabases where dbid>4 and name not in('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')  
-- Include the db's in NOT IN clause, which are suspect or inaccessible.
 
Open dbcursor
Fetch next from dbcursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
 
    set @MDFSourceFile = RTRIM((SELECT [filename] FROM master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=1))    -- For MDF Files
    set @cmd='XCOPY '+'"'+@MDFSourceFile+ '" "' +@MDFdestinationPath+ '"'
    PRINT @cmd
 
    set @LDFSourceFile = RTRIM((SELECT [filename] FROM master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=2)) -- For LDF Files
    set @cmd='XCOPY '+'"'+@LDFSourceFile+ '" "' +@LDFdestinationPath+ '"'
    PRINT @cmd
 
    set @count1 = (SELECT count([filename]) FROM master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid>2)
    set @count2=1
    set @fileid=3
    WHILE (@count2<=@count1)
    BEGIN
                set @NDFSourceFile = RTRIM((SELECT [filename] FROM master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=''+@fileid+'' ))  
                set @cmd='XCOPY '+'"'+@NDFSourceFile+ '" "' +@MDFdestinationPath+ '"'    
                set @count2=@count2+1
                set @fileid=@fileid+1
                PRINT @cmd                            
    END
    Fetch next from dbcursor into @dbname
    PRINT ''
    END
 
    END
 
close dbcursor
deallocate dbcursor

Output

/*==============================================================
Script 4 : Generate script - For attaching databases on Destination server.
Notes:
Run the below Query on Source server, to generate the attach script in result window for the destination server.
Query picks up all the secondary data files (.ndf).
If any secondary log files (.ldf) exists for any of the database, that path has to be
included manually in the query output.
Before you execute on the query out on destination server, make sure you validate 
the files.
You can attach a copied or detached database, when you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2012\2014 server instance, the catalog files are attached from their previous location along with the other database files.
Incase of SQLS erver 2000 database, you need to follow the link given in the addtional references.
Author - Dathuraj Pasarge.
Last Modified Date: 28th Nov 2014.
===========================================================*/declare @dbname nvarchar(500)
declare @FileName nvarchar(500)
declare @cmd nvarchar(2000)
declare @MdfPath nvarchar(500)
declare @LdfPath nvarchar(500)
declare @count int, @count2 int
declare @fileid int
/* IMP NOTE - Set the below paths as per your environment for mdf and ldf location on the destination server*/set @MdfPath='D:\MSSQL\DATA\' 
set @LdfPath='E:\MSSQL\Logs\'
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
DEALLOCATE dbcursor
END
declare dbcursor cursor for
select db_name(dbid),RTRIM([filename])  from master..sysaltfiles where dbid>4 
and db_name(dbid) not in ('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks') 
and  dbid<>32767 and fileid=1  -- Include the db's in NOT IN clause, which are suspect or inaccessible or not in scope.
Open dbcursor
Fetch next from dbcursor into @dbname,@FileName
WHILE @@FETCH_STATUS = 0
BEGIN
Begin
set @FileName= REVERSE(left(@FileName, CHARINDEX('.mdf', @FileName) + 4))
set @FileName= reverse(left(@FileName, CHARINDEX('\', @FileName) -1 ))
SET @cmd='exec sp_attach_db '+''''+@dbname+''''+', '+''''+@MdfPath+''+@FileName+''','
SET @FileName=RTRIM((SELECT filename from master..sysaltfiles where fileid=2 and db_name(dbid)=@dbname))
set @FileName= REVERSE(left(@FileName, CHARINDEX('.ldf', @FileName) + 4))
set @FileName= reverse(left(@FileName, CHARINDEX('\', @FileName) -1 ))
SET @cmd=@cmd + ''''+@LdfPath+''+@FileName+''''
set @count=(SELECT COUNT(*) from master..sysaltfiles where fileid>2 and db_name(dbid)=@dbname)
set @count2 =1
set @fileid=3
while(@count2<=@count)
begin
SET @FileName=RTRIM((SELECT filename from master..sysaltfiles where fileid=''+@fileid+'' and db_name(dbid)=@dbname))
set @FileName= REVERSE(left(@FileName, CHARINDEX('.ndf', @FileName) + 4))
set @FileName= reverse(left(@FileName, CHARINDEX('\', @FileName) -1 ))
SET @cmd=@cmd + ','''+@MdfPath+''+@FileName+''''
set @fileid=@fileid+1; 
set @count2=@count2+1;
end
print @cmd
print 'go'
End
Fetch next from dbcursor into @dbname,@FileName
PRINT ''
END
close dbcursor
deallocate dbcursor
 

Output

/*=================================================================
Script 5 : Changing database compatibility level.
 
Run it on the Source server, and save the query output.
Query output should be executed on the destination server.
 
Update the @cmptlevel variable with the required compatibiltiy level you want.
 
Author : Dathuraj Pasarge
Last Edited : 14th July 2014
==================================================================*/ 
declare @dbname varchar(250)  
declare @cmptlevel varchar(10)
declare @Counter int
declare @cmd varchar(250)
 
-- Set Counter to Zero
Select @Counter = 0
 
SET @cmptlevel = 110  -- Change compatibility 90 for SQL 2005, 100 for SQL 2008 and 2008 R2, 110 for SQL 2012 and 120 for SQL 2014.
 
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
 DEALLOCATE dbcursor
 END
 
declare dbcursor cursor for
select sd.name  from master..sysdatabases sd
where sd.dbid>4 and sd.name not in('ReportServer','ReportServerTempDB',
'pubs','Northwind','AdventureWorks') -- Include databases with readonly,offline ones in this NOT IN clause, or make them online.
 
Open dbcursor
 
Fetch next from dbcursor into @dbname
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
Begin
 
set @cmd='alter database '+ @dbname+ ' set compatibility_level = '+ @cmptlevel
Select @Counter = @Counter + 1
 
print @cmd
End
 
Fetch next from dbcursor into @dbname
END
 
close dbcursor
deallocate dbcursor

Ouput

/*=============================================================
Script 6 : Generate script - Update usage on all the databases.
 
Important notes –
Run it on the Source server, and save the query output.
Query output should be executed on the destination server.
Execute only incase you are migrating the databases from SQL 2000 to any higher SQL versions.
 
Author : Dathuraj Pasarge
Last Edited : 14th July 2014
===============================================================*/ 
DECLARE @SQL VARCHAR(1000)  
DECLARE @DB sysname  
 
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
 DEALLOCATE dbcursor
 END
 
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
 
SELECT [name] FROM master..sysdatabases WHERE dbid>4 and [name] not in('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')  ORDER BY [name]  -- Include the db's in NOT IN clause, which are suspect or inaccessible.
 
OPEN curDB  
    FETCH NEXT FROM curDB INTO @DB  
    WHILE @@FETCH_STATUS = 0  
BEGIN  
        
    SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'DBCC updateusage(0)' + CHAR(13)  
        
        PRINT @SQL  
          
    FETCH NEXT FROM curDB INTO @DB     
 
END  
    
CLOSE curDB  
DEALLOCATE curDB

Output

/*=============================================================
Script 7 : Generate script - Update statistics on all the databases.
 
Important Notes:
Run it on the Source server, and save the query output for final execution on the destination server.
 
Author : Dathuraj Pasarge
Last Edited : 14th July 2014
===============================================================*/ 
DECLARE @SQL VARCHAR(1000)  
DECLARE @DB sysname  
 
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
 DEALLOCATE dbcursor
 END
 
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name] FROM master..sysdatabases WHERE dbid>4 and  [name] NOT IN ('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks')
                    ORDER BY [name] -- Include the db's in NOT IN clause, which are suspect or inaccessible.
 
OPEN curDB  
    FETCH NEXT FROM curDB INTO @DB  
    WHILE @@FETCH_STATUS = 0  
BEGIN        
    SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13)         
        PRINT @SQL           
    FETCH NEXT FROM curDB INTO @DB  
    
END  
    
CLOSE curDB  
DEALLOCATE curDB

Output

/*============================================================
Script 8 : Generate script - for changing all user database owners.
 
Important Notes:
Run it on the Source server, and save the query output, if required make necessary  
changes in the output file with the required owner ( foe ex : sa, service account etc).
 
Author : Dathuraj Pasarge
===============================================================*/ 
declare @dbname varchar(250)  
declare @login varchar(250)  
declare @cmd varchar(250)
 
IF CURSOR_STATUS('global','dbcursor')>=-1
BEGIN
CLOSE  dbcursor
 DEALLOCATE dbcursor
 END
 
declare dbcursor cursor for
select sd.name, suser_sname(sd.sid) from master..sysdatabases sd
where sd.dbid>4 and sd.name NOT IN ('ReportServer','ReportServerTempDB','pubs','Northwind','AdventureWorks') -- Include the db's in NOT IN clause, which are suspect or inaccessible.
 
Open dbcursor
 
Fetch next from dbcursor into @dbname,@login
 
WHILE @@FETCH_STATUS = 0
BEGIN
Begin
 
print 'use ' + @dbname
set @cmd='exec sp_changedbowner '+''''+@login+''''
print @cmd
End
 
Fetch next from dbcursor into @dbname,@login
END
 
close dbcursor
deallocate dbcursor

Output

Execute the below scripts and steps during actual change window :

1.Take all system and user database backups (may be couple of hours or before the change window, it all depends on no. of databases and their sizes and agreed change window).  

2.Request App team to stop the application services.

3. Kill all user sessions on the source server.

/*=========================================================================
Script 9 :
Kills all user connections on the source sql instance.
 
Before detaching the databases, all user sessions must be disconnected with the below query.
If possible stop the agent service.
 
 Author : Dathuraj Pasarge.
Last Modified Date: 10th Nov 2014.
 =========================================================================*/DECLARE @kill varchar(4000)  
set @kill= '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses  
WHERE spid >50 and spid<>(select @@SPID)
EXEC(@kill);
go  
use master
go
sp_who2;
go

4. Detach all user databases from the source server.

Execute Query output of - Script # 1.

5.  Generate XCopy commands for copying all user database mdf, ldf and ndf files from source server to destination server. The query out-put has to be executed from the command prompt [run as administrator].

Execute Query output of - Script # 3.  

Note : Compare db files count (mdf , ldf and ndf) with the help of excel sheet data reference. Create additional XCopy commands incase of more than 1 ldf exists for any your database.

6. Attach databases on destination server.

Execute Query output of - Script # 4.

7. Once attach is done, make sure that all databases are online and accessible.  

8. If you see any error while attaching a database, make sure that no. of database files are copied over to the destination server, and your attach script is correct for that db. Verify the physical file names from the excel sheet.

9. Execute login scripts, SQL Agent jobs, linked servers, Operators etc on destination SQL instance.

Execute all the scripts that were taken in the STEP 2.

10. Fix orphaned users.

/*===========================================================
Script 10 - Fix Orphaned users.
Notes:
1. The below query fix the ophaned issue on all the databases.
2.  Run the below query only after you created all the logins on the detination sql instance.[In case of migration scenario].
3. Script will ignore the users, that dont have a login with the same name.
 
Author: Dathuraj Pasarge
==========================================================*/ 
SET NOCOUNT ON
If exists (select * from sysobjects where name like '#databases%')
 DROP TABLE #databases
 if exists (select * from sysobjects where name like '#orphanusers%')
 DROP TABLE #orphanusers
BEGIN
 
declare @UserName sysname,
@DBName varchar(250),
@NoOfUsers smallint,
@Query1 varchar(2000),
@Query2 varchar(2000)
 
CREATE TABLE #orphanusers
(
rowid smallint IDENTITY(1,1),
UserName sysname,
UserSID varbinary(100)
)
 
CREATE TABLE #databases
(
dbname varchar(250)
)
 
INSERT INTO #databases SELECT name from master.sys.databases where database_id > 4 and state_desc = 'ONLINE'
 
WHILE EXISTS(SELECT 1 FROM #databases)
 
BEGIN
 
 SET @Query1 = ''
 
 SELECT TOP 1 @DBName = dbname FROM #databases ORDER BY dbname
 
 SET @Query1 = 'EXEC ' + @DBName + '.dbo.sp_change_users_login ''report'''
 
 INSERT INTO #orphanusers EXEC(@Query1)
 
  WHILE EXISTS(SELECT 1 FROM #orphanusers)
BEGIN
SELECT TOP 1 @UserName = UserName
FROM #orphanusers ORDER BY rowid
 
BEGIN TRY
SET @Query2 = 'EXEC ' + @DBName + '.dbo.sp_change_users_login ''Update_One'',' + @UserName+ ',' + @UserName
EXEC(@Query2)
Print 'Orphaned user ' + @username + ' fixed in ' +@DBName +' database'
END TRY
 
BEGIN CATCH
 
PRINT 'Login '+ @username + ' has not found. If needed , create it and fix the orphaned issue. '
 
END CATCH
      DELETE FROM #orphanusers WHERE UserName = @UserName
END
      DELETE FROM #databases WHERE dbname = @DBName
END
 
DROP TABLE #orphanusers
DROP TABLE #databases
 
END

11. Next change database compatibility.

Execute Query output of - Script # 5.  

12. Next change db owners for all user databases.

Execute Query output of - Script # 6.  

13. Next execute DBCC Updateusage  

 Execute Query output of - Script # 7.  

14. Next execute Updatestats commands.  

 Execute Query output of - Script # 8.  

Post migration check list:

If you see slowness in the application, and have enough downtime try running the rebuild index and update stats with full scan by creating maintainance plan or any other best possible method you feel.

1.    Check  SQL Server Error Log for login failures and other errors.

2.    If you see the below error while attaching a database, open SSMS as "Run as Administrator".

              Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".

3.    If  you unable to resolve the hostname, you can use the ipconfig /flushdns command to flush and reset the contents of the Domain Name System (DNS) client resolver cache.

4. Make sure ODBC connections are working (if exists). 

5.    Make sure linked servers are working (if exists).  

Additional References:

Fulltext Seach catalog movement:

SQL Server 2000:

http://support.microsoft.com/kb/240867

SQL Server 2005:

https://msdn.microsoft.com/en-us/library/ms345483%28v=sql.90%29.aspx

DTS Packages migration:

https://msdn.microsoft.com/en-us/library/ms143496%28v=sql.105%29.aspx

Hope this helps to you all..!!!