Migrating MS-SQL 2008 to 2012


  • Update the new servers to have the latest updates for both OS and MS-SQL
  • Setup Windows Failsafe cluster
  • Setup Always-On availability group, replicas and listener
  • Setup shared folder for storing backups from the old database servers
  • Setup shared folder for storing backup/synchronization data for the new database servers
  • Create a copy of the stored procedures used by jobs, and update them to work with availability group
  • Script the SQL server logins
  • If you want to change the databases to read-only mode, remove existing mirroring functionality for the databases


  1. Put up App_Offline.htm file for the web servers
  2. Take full backup of the database

    BACKUP DATABASE [{DatabaseName}]
            TO DISK = N'{BackupSharedFolder}\{DatabaseName}.bak'
            WITH NOFORMAT, 
            NAME=N'{DatabaseName}-Full Database Backup', 
  3. Take Transactional backup of the database

    BACKUP LOG [{DatabaseName}]
            TO DISK = N'{BackupSharedFolder}\{DatabaseName}.log'
            WITH NOFORMAT, 
            NAME = N'{DatabaseName}-Transactional Backup', 
  4. Set database to read-only mode

  5. Connect to the new server

  6. Restore the database

    DECLARE @v_DefaultDataPath NVARCHAR(250) = CONVERT(NVARCHAR,SERVERPROPERTY('InstanceDefaultDataPath'));
                DECLARE @v_DefaultLogPath NVARCHAR(250) = CONVERT(NVARCHAR,SERVERPROPERTY('InstanceDefaultLogPath'));
                DECLARE @v_FinalDataFilePath NVARCHAR(250) = CONCAT(@v_DefaultDataPath,'\{DatabaseName}.mdf');
                DECLARE @v_FinalLogFilePath NVARCHAR(250) = CONCAT(@v_DefaultLogPath,'\{DatabaseName}_Log.ldf');
                DECLARE @v_LogicalDataFileName NVARCHAR(128), @v_LogicalLogFileName NVARCHAR(128);
                DECLARE @tbl_FileList TABLE
                      LogicalName NVARCHAR(128) ,
                      PhysicalName NVARCHAR(260) ,
                      [Type] CHAR(1) ,
                      FileGroupName NVARCHAR(128) ,
                      Size NUMERIC(20, 0) ,
                      MaxSize NUMERIC(20, 0) ,
                      FileID BIGINT ,
                      CreateLSN NUMERIC(25, 0) ,
                      DropLSN NUMERIC(25, 0) ,
                      UniqueID UNIQUEIDENTIFIER ,
                      ReadOnlyLSN NUMERIC(25, 0) ,
                      ReadWriteLSN NUMERIC(25, 0) ,
                      BackupSizeInBytes BIGINT ,
                      SourceBlockSize INT ,
                      FileGroupID INT ,
                      LogGroupGUID UNIQUEIDENTIFIER ,
                      DifferentialBaseLSN NUMERIC(25, 0) ,
                      DifferentialBaseGUID UNIQUEIDENTIFIER ,
                      IsReadOnl BIT ,
                      IsPresent BIT ,
                      TDEThumbprint VARBINARY(32)
                INSERT  INTO @tbl_FileList
                            ( 'RESTORE FILELISTONLY FROM DISK = N''{BackupSharedFolder}\{DatabaseName}.bak'''
                -- Extract the logical data file name from the backup file
                SELECT  @v_LogicalDataFileName = LogicalName
                FROM    @tbl_FileList AS tfl
                WHERE   [tfl].[Type] = 'D';
                -- Extract the logical log file name from the backup file
                SELECT  @v_LogicalLogFileName = LogicalName
                FROM    @tbl_FileList AS tfl
                WHERE   [tfl].[Type] = 'L';
                RESTORE DATABASE [EyeTeaERP] 
                    FROM  DISK = N'{BackupSharedFolder}\{DatabaseName}.bak' 
                    WITH  FILE = 1,  
                    MOVE @v_LogicalDataFileName TO @v_FinalDataFilePath,  
                    MOVE @v_LogicalLogFileName TO @v_FinalLogFilePath,  
                RESTORE LOG [{DatabaseName}] 
                    FROM  DISK = N'{BackupSharedFolder}\{DatabaseName}.log' 
                    WITH  FILE = 1,  
  7. Set the compatibility level matching the new version

    DECLARE @v_CompLevel INT = 0;
          SELECT  @v_CompLevel = [d].[compatibility_level]
          FROM    [sys].[databases] AS d
          WHERE   [d].[name] = '{DatabaseName}';
          IF @v_CompLevel < 110
              -- 100 = 2008, 110 = 2012
                  ALTER DATABASE [{DatabaseName}] SET COMPATIBILITY_LEVEL = 110;
  8. Add the database to the availability group

    ALTER AVAILABILITY GROUP [{AvailabilityGroupName}] ADD DATABASE [{DatabaseName}];
  9. Take a full backup and transactional backup of the database to the synch folder share

  10. Connect to each of the secondary replicas

    1. Restore the database from the synch folder share in recovering mode sql RESTORE ... --AS ABOVE EXAMPLE + WITH NORECOVERY
    2. Add the database to the availability group sql ALTER DATABASE [{DatabaseName}] SET HADR AVAILABILITY GROUP = [{AvailabilityGroupName}];
  11. Restore server logins as needed on all replicas

  12. Connect to the availabiliy group and update the stored procedures with the modified copy created for working with Always-On availability group

  13. Setup the database mail, and all operators as needed in all of the replicas

  14. Restore the jobs to all of the replicas


  • Update the configuration files connections string to point to the correct data source
  • Remove the App_Offline.htm file from the web servers
  • Confirm the failover is working as intended
  • Confirm the jobs are working as intended


Package the above steps through an application created in language of your choice with care to set Connection Timeout in the connection string, and also appropriate CommandTimeout you are all set to go!

comments powered by Disqus