I think this maybe an option
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/355e56e3-3d24-472f-903b-87618de3a1a6/importexport-userlogin-credentials-and-passwords?forum=sqldatabaseengine this is a PERFECT answer. It worked flawlessly. Just to be clear what I did: 1. I backed up the database (full data backup) from the source. 2. I ran the script as indicated by microsoft to produce a script (script A): http://support.microsoft.com/kb/918992/ 3. I ran the following script and saved each row of the results as one script (script B): select 'exec master.dbo.sp_addsrvrolemember @loginame=''' + member.name + ''', @rolename=''' + roles.name + '''' from sys.server_role_members join sys.server_principals roles on role_principal_id = roles.principal_id join sys.server_principals member on member_principal_id = member.principal_id 4. I restored the full data backup to the destination. 5. I ran script A against the destination. 6. I ran script B against the destination. What this did was built the logins, and kept the password, Server Roles and User Mapping confiugrations for all the logins. Brilliant and flawless. Thanks Jonathan! Jean-Paul Natola From: [email protected] To: [email protected] Subject: [NTSysADM] RE: sql server upgrade Date: Tue, 22 Oct 2013 17:14:51 +0000 This just a bit of additional information regarding the SQL server migration as we discussed below. I –finally- did the migration last night using option #2 as discussed. Things went mostly well. The one thing I didn’t anticipate was that detaching the DBs from the old server and attaching the DBs on the new server wasn’t quite enough for the various applications to run, even when the new SQL server had the same server name and IP address. Reason being, the old SQL server had several user accounts/logins created WITHIN SQL during the time the applications(s) were initially setup and DBs were created. Those accounts/logins within SQL had specific security contexts and levels for the different databases on the old server. Attaching the DBs to the new server did not automatically create those SQL accounts/logins on the new SQL server, so many of the applications did not function until we called the app-vendors themselves and explained our migration process. The vendors were however pretty good in regards to knowing exactly how to manually create those SQL accounts/logins within the new SQL server and get their respective applications running again fairly quick. So, a word of caution to anyone migrating over DBs from one SQL server to another, check the accounts that were created within the SQL mgmt. console and plan for getting them recreated on the new SQL server, else your apps won’t function. JR Sent: Monday, August 12, 2013 2:52 PM To: [email protected] Subject: [NTSysADM] RE: sql server upgrade thanks damien and glen. i think i'll go ahead with option #2. i already have the MS tech article on how to rename a sql server so that isn't a concern. appreciate the insight. jesse From: [email protected] [[email protected]] on behalf of Glen Johnson [[email protected]] Sent: Monday, August 12, 2013 2:43 PM To: [email protected] Subject: [NTSysADM] RE: sql server upgrade +100 on option 2. I personally wouldn’t trust option one, way too many strange things happen later and you never know if they are related to the upgrade or not. Plus, I never do OS upgrades. Never, never, never. From: [email protected] [mailto:[email protected]] On Behalf Of Jesse Rink Sent: Monday, August 12, 2013 3:34 PM To: [email protected] Subject: [NTSysADM] sql server upgrade We have an older Windows 2003 R2 x64 VM server which contains SQL2005 x64 on it. I'm planning on getting this box more current... My first option is to: 1. Take a image backup of the VM with our PHD Virtual software 2. Increase the c: drive hard disk in vSphere and then use a partition tool to expand the c: partition (its too small to perform the 2008 R2 upgrade on it) 3. Upgrade 2003 R2 x64 to Windows 2008 R2. 4. Upgrade SQL 2005 to SQL 2008 R2. Anyone had bad experiences going this route? The second option is to: 1. Create a brand new 2008 R2 server with SQL 2008 R2. 2. Detach the 10 DBs from SQL-OLD, copy them over to the SQL-NEW server and re-attach. 3. Decomission the old SQL-OLD server 4. Rename SQL-NEW to SQL-OLD and assign it the same IP address that the original SQL-OLD had. 5. Setup my Maintenance Plans from scratch. I think I'll be good for all my client applications that have specific ODBC settings configured pointing to either the IP address of the SQL box or the computer name/FQDN. Anyone had bad experiences going this route? Leaning more towards option TWO at this time... I tend to like clean installs as opposed to upgrades (generally). JR
