atorex
asked on
SQL 2008 users migration
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.
ibrahim52
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.
ASKER
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.
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.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.