Database Migration SQL Server 2005 – 2008 – Step by Step

Do you worked on database server migration? ie from SQL Server lower version to higher version? Here I am going to explain about step by step procedures of migrating SQL Server 2005 to SQL Server 2008 R2.

 

  1. At first look into the Microsoft new product Information, i.e. new features in the product box.
  2. Identify what the benefits are getting to customer by moving the database from lower version to higher version.
  3. Identify the downtime needed, if necessary.
  4. First step is to script the logins from Source SQL Server, i.e. SQL Server 2005. The steps are given below.
    1. In the source server, run the below query in the master database.

 

USE master 
 
go 
 
IF Object_id ('sp_hexadecimal') IS NOT NULL 
  DROP PROCEDURE sp_hexadecimal 
 
go 
 
CREATE PROCEDURE Sp_hexadecimal @binvalue VARBINARY(256), 
                                @hexvalue VARCHAR (514) output 
AS 
    DECLARE @charvalue VARCHAR (514) 
    DECLARE @i INT 
    DECLARE @length INT 
    DECLARE @hexstring CHAR(16) 
 
    SELECT @charvalue = '0x' 
 
    SELECT @i = 1 
 
    SELECT @length = Datalength (@binvalue) 
 
    SELECT @hexstring = '0123456789ABCDEF' 
 
    WHILE ( @i <= @length ) 
      BEGIN 
          DECLARE @tempint INT 
          DECLARE @firstint INT 
          DECLARE @secondint INT 
 
          SELECT @tempint = CONVERT(INT, Substring(@binvalue, @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 @hexvalue = @charvalue 
 
go 
 
IF Object_id ('sp_help_revlogin') IS NOT NULL 
  DROP PROCEDURE sp_help_revlogin 
 
go 
 
CREATE PROCEDURE Sp_help_revlogin @login_name SYSNAME = NULL 
AS 
    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 @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.syslogins 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 
 
          RETURN -1 
      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 
 
                      EXEC Sp_hexadecimal 
                        @SID_varbinary, 
                        @SID_string out 
 
                      -- 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 
 
    RETURN 0 
 
go
  • Execute the procedure Exec sp_hexadecimal in master database of the source server.
  • The output of the procedure is a script to create logins.
  • Execute the above script in the destination server to create logins.
  • Backup the databases from source server and restore it into the destination server.
  • On each database context (databases which is restored) run the stored procedure sp_change_users_login and map the orphaned users with the login. Learn more about sp_change_users_login http://technet.microsoft.com/en-us/library/ms174378(v=sql.100).aspx
  • Change the compatibility level to 100 if, you are restoring to SQL Server 2008 R2 or SQL Server 2008 from SQL Server 2005. Please run the below query to change the compatibility level
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 100

If you have any best practices, you are always welcome to discuss.