Solved

SQL 2008 users migration

Posted on 2013-01-04
5
335 Views
Last Modified: 2016-02-11
I have to move a database server running SQL 2008 to a new hardware server what is the best process to import all users and configs to the new server. the database on the server will be moved by moving the files and reattaching the DB, however before I do this I want to restore a backup on the new server and test functionality with things like report services that uses IIS site for reports and so on. I would like to make sure I have all the required users imported and any configs from the current production system.
0
Comment
Question by:atorex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 12

Expert Comment

by:ibrahim52
ID: 38744020
Simply just move .mdf & log file to the new server but just make sure of SQL Server edition, it should be similar on your new server as well.
0
 

Author Comment

by:atorex
ID: 38744052
Are you saying that the production MDF/LDF will contain all users for that SQL server?
is there a way I can import users for testing as I cant copy the current mdf during operations and due to size cant take the server down multiple times for the time I will need to copy.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38744157
Just moving the mdf/ldf of your user database will move the users of the database, but not the instance logins.

To move your instance logins, Microsoft has a KB article explaining exactly how to do it:
http://support.microsoft.com/kb/918992

However, after doing that, you will likely have a disconnect between logins and users.

Fixing orphaned users is detailed here:
http://msdn.microsoft.com/en-us/library/ms175475.aspx

I recently had to move a bunch of databases between servers.  After installing SQL Server on the new machine, I recreated my logins, restored the databases from backups taken on the source machine and fixed my orphaned users and everything came up fine.
0
 

Author Comment

by:atorex
ID: 38744191
I have a restore running now, I will follow the provided steps and see what I come up with.
my only user concern is a user IIS uses to retrieve reporting data these reports have to be done each day for upper management and I want to make double sure it will work, thats why I'm a little freakish about it.
0
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 500 total points
ID: 38749965
using the SSIS you can move lot of SQL Objects like Database, Logins, Error Log, SQL Jobs, System Master SPs etc.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Getting local user timezone in Sql Server 5 31
Windows server 2008 exchange 3 30
Problem to Citrix 2 22
Please explain Equi-join 3 17
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question