Answered by:
Migration

Question
-
Hi,
I am planning for a data migration.
We have a application database - SQL Server 2008 (290 tables)
Application new version changes includes
21 new tables and changes for 14 existing tables.
my plan - please correct me whether below plan is ok. Thanks!
1) Restore existing database in new datacenter (we are moving existing prodution database to different databse)
2) Transfer all logins and sql jobs (we have no ssis packages)
3) Apply all delta scripts and then apply migration change scripts.
4) Test the applicaton
KishoreTuesday, July 5, 2011 3:15 PM
Answers
-
After restoring the database, before transferring the logins, Please make sure to fix orphan logins, you can do it by using the following queries
You can run this query first to identify the list of orphaned users in restored database
EXEC sp_change_users_login 'REPORT'
Then you can use this query to fix the logins
EXEC sp_change_users_login 'UPDATE_ONE','youruser','youruser'
Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"Wednesday, July 6, 2011 2:09 PM
All replies
-
Sounds good...
http://uk.linkedin.com/in/ramjadduTuesday, July 5, 2011 8:59 PM -
After restoring the database, before transferring the logins, Please make sure to fix orphan logins, you can do it by using the following queries
You can run this query first to identify the list of orphaned users in restored database
EXEC sp_change_users_login 'REPORT'
Then you can use this query to fix the logins
EXEC sp_change_users_login 'UPDATE_ONE','youruser','youruser'
Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"Wednesday, July 6, 2011 2:09 PM -
Kindly check below guidelines to Migrate Server :-
1) If domain is different ..There would be possibility SQL Jobs will not be run .
2) All User will not be migrated due to different domain and those will not be mapped with any script so We will have to create again in new domain. [ If it's in Same Domain Migration there would not be any problem and login will be mapped with above script ]
EXEC sp_change_users_login 'REPORT'
Then you can use this query to fix the logins
EXEC sp_change_users_login 'UPDATE_ONE','youruser','youruser'
KuldeepWednesday, July 6, 2011 2:45 PM