Transfer a SQL Database to new SQL Server

Let’s say you want to update your current DotNetNuke portal to a newer version. Then you shouldn’t do it in the production environment. But when you restore the database backup on the development database server the database logins with SQL users do not work anymore.

Microsoft has published a knowledge base article on transferring SQL logins from one server to another.

1. Create a custom stored procedure to export the current login information at the source server
2. Create a new login on the target SQL server for the database you intend to migrate

When you run the SQL script to create the stored procedures, make sure that you add the SPs to the master database.

The SP sp_help_revlogin generates a SQL login script for each login available on the source server at the time of execution. Just copy the login script for the required user to the development server and execute the script.

EXEC sp_help_revlogin

Create login

CREATE LOGIN [databaseuser] WITH PASSWORD = 0x0200E0B731A01A580B19D058948AEA69FF2CBBA5F62F9F3DC72AFCDA95C5CE52EBA8C971DEFC7A1B9A7A6776FC757A28DDD0EAB8C57E2EBF72F74BB0460BF0DC404ACC9D26A8 HASHED, SID = 0xFEF02A527A90384095BB5DF8CC1713DF, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

It contains the password hash, and this makes it possible to create a new login with the same (still unknown) password from the old login. Since this is only a password hash and not the actual password, this won’t actually corrupt the security of your account. Restore your database backup to the development server.

Now run the generated SQL login script on the development server. In this stage, you have transferred SQL login, which is known to the server itself, and a database user, which is known to the database.

To connect the login with the user, execute the following command in the context of your restored database:

ALTER USER [DATABASEUSERNAME] WITH LOGIN = [SQLLOGINNAME]

To access the restored database for any other DotNetNuke update and upgrade fun, change the database server name in the web.config of your web application.

Yes, the DotNetNuke web application should be copied from the production server to the development server.

Links

• How to transfer logins and passwords between instances of SQL Server
• Contained Databases
• Download current release of SQL Server Management Studio (SSMS)

Enjoy SQL Server.