Migrate Ms SQL Server Databases

Sometimes we have to migrate the contents of a Ms SQL instance from one server to another. Often we don't know the credentials of the single databases to be migrated. Sometimes we need to create a second server, in line with the original, and change at the last minute the server name and instance name to make it identical to the previous. Using some scripts we can easily make this.

We need to connect with SQL Management Studio to the instance to be migrated with sa user. Clean up the database log before starting migration operations.

In the new SQL server, load the previous database backups. Execute the script created to insert the login users. Probably, users will be present but not connected to their databases. They are orphan users. With the following script we can combine again users to their databases.

After completing tests on migrated databases, shut down the old server and change the name and IP of the new server to take over it. As a final activity, we must update the instance data with the new server name, using these commands: