SQL Server Database Migration Checklist

This guide is meant to provide general recommendations for migrating SQL Server databases from one server to another.

Always test database migrations (and subsequent application connectivity) in a lab environment prior to the actual migration.

Note: Server name ‘SOURCE’ is the original SQL Server, ‘TARGET’ is the new SQL Server.

1.1 Database migration checklist

The migration should be conducted in three phases:

1) Pre-migration inventory / checks

2) Database migration

3) Post-migration configuration / checks

1.1.1 Pre-migration phase

For the pre-migration phase, review and document the following tasks on the SOURCE server.

1) Database sizes

2) Data and Log file location

3) Server and Database properties (Collation, Auto Stats, DB Owner, Recovery Model, Compatibility level, etc.)

4) Collect the information of dependent applications, make sure application services will be stopped during the database migration

5) Database logins, users and their permissions

6) Dependent objects (SQL Agent Jobs and Linked Servers)

7) Document maintenance plans

On the TARGET server, conduct the following tasks:

1) Verify adequate disk space exists

2) Appropriate folders have been created

3) SQL Server is correctly installed and configured with latest Service Pack and Cumulative Update

4) Set SQL Server properties; memory, tempdb size, autogrowth and drive location.

5) Appropriate connectivity exists with application servers, web servers, etc.

6) Create databases consistent with planned database migrations

1.1.2 Database migration phase

1) From SOURCE server, get recent list of DB and file location list

2) On SOURCE SQL Server, Script migrate user LOGIN information between servers, save output for subsequent step.

3) Use native SQL Server to back up databases from SOURCE, use backup compression to reduce size of backup (optionally, use detach database, copy db files, then reattach database)

4) On SOURCE server, place databases in Read Only mode

5) Restore databases to TARGET

6) On TARGET SQL, apply script to migrate users/group security (Created in Step 3)

7) Update any custom SSRS DSNs to point to TARGET server.

8) On SOURCE server, script any required SQL Server Agent Jobs (select Job, right click, Script Job as > CREATE to > File ) to create

9) On TARGET, using the file create, then transfer/execute job creation on TARGET. Create schedules as needed.

1.1.3 Post-migration phase

1) Point the application to the new DB server address, or Availability Group LISTENER for AlwaysOn Availability Groups (Connection strings, etc. to altered by the application support team)

2) Restart / review Network connections between all stake holding servers (Network Team)

3) Check the SQL Server Error Log and Windows Error logs for any failures

a. Confirm application functionality with end users

1.2 Transfer logins

If you ever have a need to migrate SQL Server databases between servers; particularly a backup/restore scenario, you need to be aware that the database security login ids are not automatically migrated. Here are some notes on this task.

Once databases are backed up from one instance and restored to another instance, database security needs to be transferred as well.

This task can be a challenge, fortunately Microsoft has created a SQL script that can be used for this purpose. This can be found here:

How to transfer logins and passwords between instances of SQL Server

The process is well documented. The steps are as follows:

1. From the transfer logins and passwords web page, copy/paste the SQL script to SQL Server Management Studio (SSMS) running on the SOURCE server.

2. Execute the script – it will create a new stored procedures on the Master database.

3. From SSMS, run the following query:

4. EXEC sp_help_revlogin

5. The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

6. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

7. Important Before you go to step 5, review the information in the "Remarks" section. (Note: see web page mentioned earlier)

8. Open a new Query Editor window on TARGET server, and then run the output script that is generated in step 3.

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