Your company's ad could live here and reach over 50,000 people a month!

Share This Post

Migrating databases between SQL Server instances

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:

EXEC sp_help_revlogin

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.

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

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

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

Filed under: ConfigMgr, SQL, SQL Server, SQL Server 2012, SQL Server 2014

Share This Post

Leave a Reply