1

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.**

  • 1
    I tried to use the copy database wizard and it keep failing. --> can you elaborate this sentence? What error message did you get when using database wizard? Please see How can I ask better questions on Server Fault? – masegaloeh Dec 3 '14 at 22:10
  • How many databases are we talking about? – Katherine Villyard Dec 3 '14 at 22:14
  • Hello Katherine, sorry about the confusion, it is about 10 databases – cmkony04 Dec 4 '14 at 0:56
  • Hello Masegaloeh, sorry about the lack of information provided, but I edited my question. Thanks for your help – cmkony04 Dec 4 '14 at 0:58
2

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.

4

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')
1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.