Migrating Mssql databases from different instances to a new server

Question

I have 3 instances of Sql (2008 R2) on 3 different servers. The versions are (10.50.2550.0 SP1 Standard Edition (64-bit), 10.50.2550.0 SP1 Standard Edition (64-bit)). The goal is to migrate all the databases and logins to new server, we just purchase with Sql server 2012.

I don’t know how to approach, I tried to use the copy database wizard and it keep failing. If I decide to backup each database and restore them to new the servers, I don’t know how to migrate the logins.

What is the best approach to deal with the situation?

Sorry about the confusion: (More details about the issue)

1. Number of databases to be moved : 10
2. Error listed during the copy database wizard:

Line 60:

OnError,DUPFAIDS,NT AUTHORITY\SYSTEM,DUAPPSRV1_EXAPPSVR1_DUPFAIDS_PFAIDS_Transfer Objects Task,{8F0C82BB-4C5D-4796-BE75-BAB033C59039},{3818CD1B-9685-46B7-AFE2-AE0FE962DA03},

12/3/2014 12:10:15 PM,12/3/2014 12:10:15 PM,0,0x,ERROR : errorCode=-1073548784

description=Executing the query "--------------------------------------------------..." failed with the following error: "Invalid object name 'xwd_window_disabled_objects'.".

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.**

Answer 1

Backup and Restore the databases as suggested. Then run this microsoft script (https://support.microsoft.com/kb/918992) on the old servers to generate a script to create the logins on the new server. Since you have two source servers, be sure to review your script carefully to make sure there aren't any duplicate entries or other issues.

Answer 2

There are a lot of different ways to migrate the databases. Backup and restore is the most commonly recommended, but if you have more than 100 databases you might want to script something to detach and attach the databases. I've successfully used this to migrate a very large number (hundreds) of databases to a new server:

@ECHO ON

set controlfile=control.txt

set newipmdf=\\newserver\g$
set newipldf=\\newserver\e$
set oldserver=oldserver\Prod1
set oldmdfpath=d:\prod1
set newmdfpath=g:\data
set copymdfpath=m:\data
set newserver=newserver
set oldlogpath=e:\prod1
set newlogpath=e:\log
set copylogpath=l:\log
set movedmdfpath=%oldmdfpath%\moved
set movedldfpath=%oldlogpath%\moved

mkdir %movedmdfpath%
mkdir %movedldfpath%

net use m: %newipmdf%
net use l: %newipldf%

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
  SET "line=%%L"
  SETLOCAL ENABLEDELAYEDEXPANSION
  ECHO !line!
  sqlcmd -E -S!oldserver! -Q"EXEC master.dbo.sp_detach_db @dbname = N'!line!'"
  copy "!oldmdfpath!\!line!.mdf" !copymdfpath!
  copy "!oldlogpath!\!line!_log.ldf" !copylogpath!
  sqlcmd -E -S!newserver! -Q"CREATE DATABASE [!line!] ON ( FILENAME = N'!newmdfpath!\!line!.mdf' ),( FILENAME = N'!newlogpath!\!line!_log.ldf' ) FOR ATTACH"
  move "!oldmdfpath!\!line!.mdf" !movedmdfpath!
  move "!oldlogpath!\!line!_log.ldf" !movedldfpath!
  ENDLOCAL
)
ENDLOCAL

net use m: /z
net use l: /z

As for the logins, I can't help you with the passwords but you should be able to get a list of usernames with:

SELECT [name], type, type_desc
  FROM [master].[sys].[server_principals]
  where is_disabled=0 and type in ('S','U')

Answer 3

Katherine Villyard's script for detaching / attaching is good.

If you want to backup and restore that is also a good option that is frequently used. But do the logins first.

Copy the code for sp_hexadecimal and sp_help_revlogin from this Technet article and use sp_help_revlogin to generate TSQL statements that you can use to copy your logins over. The process is this:

1. Run the code mentioned above to create the stored procedures (in master)
2. Run EXEC sp_help_revlogin on your old server
3. Copy the results of sp_help_revlogin and paste them in a new query window connected to your new SQL Server instance
4. Run the TSQL statements you just pasted

If done correctly your new instance will have matching logins (with matching SIDs and passwords, this is key). Then you can backup and restore your databases to the new instance without worrying about orphaned database logins.

(NOTE to future visitors: if for some reason the link breaks just do a search for sp_help_revlogin to find the new page).