April 26, 2011 at 3:29 am
Hi,
I need to migrate Logins, Jobs, Databases etc from an installation of SQL Server 2005 Standard Edition on 32 bit to SQL Server Enterprise Edition on 64 bit on another machine. What are the steps I should follow here.
Thanks in Advance !
April 26, 2011 at 4:43 am
1-User Databases backup
2-Jobs Backup as attachment shown
3-Linked Server script Backup if any same as job backup
4-Logins script backup same as job backup
5-Database Trigger script Backup if any Right click to create an script
then restore all user databases on another server ,note it when you restore database on another server then user will move automatically with database but these user must be mapped or drop before server user access these databases
USE [DATABASE NAME]
GO
DROP USER
GO
user should drop before you create a same user as old server
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 26, 2011 at 4:52 am
If you're going to script the logins, don't use the 'script login as...', you also need to script the password and the SID for any SQL accounts you have. Otherwise no one will be able to log in, and all the users in every database will be orphaned.
If you script the logins with the SIDs, then there's no need to drop and create users again (a difficult job if they own schema, and would also require you to recreate all their roles and permissions)
See http://support.microsoft.com/kb/918992 and http://sqlblog.com/blogs/eric_johnson/archive/2008/09/30/scripting-sql-server-logins.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2011 at 4:56 am
yes i forget to mention you have to change the password in the login script because when you generate an script then this will have encrypted password,so change as you required before apply on the database
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 26, 2011 at 4:59 am
Syed Jahanzaib Bin hassan (4/26/2011)
yes i forget to mention you have to change the password in the login script because when you generate an script then this will have encrypted password,so change as you required before apply on the database
Or use the script I linked to to script out the login with the correct password hashed. That way you can create the login on the new server with the same password it had on the old, so that you don't have all sorts of fun with users unable to connect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2011 at 2:43 pm
When you restore database to a different server your Database users are orphaned.
use databasename
EXEC sp_change_users_login 'Report'
If you already have logins created in the new server then use
use databasename
EXEC sp_change_users_login 'UPDATE_ONE','UserName','Loginname'
Also if you have encryption in your tables then you need to do few more things:
use databasename
CREATE MASTER KEY ENCRYPTION BY password ='KEY'
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'KEY'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
April 26, 2011 at 2:49 pm
Apurva (4/26/2011)
When you restore database to a different server your Database users are orphaned.
Not if when the logins are created they are created with the same SID as on the original server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2011 at 2:28 am
Thank you all for your valuable replies. Yes GilaMonster. I will be using the sp_help_revlogin to transfer the logins. I will be running Upgrade advisor before the migration.
Another question I have is do I need to restore the system databases from my old server on to my new server ? Additional information - There is no high availability options configtured on this server.
April 27, 2011 at 3:02 am
I don't recommend restoring system databases to other versions.
Rather script out what you need (logins, linked servers, server-level permissions, jobs, job history if you want it, DTS/SSIS) and apply those on the new server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2011 at 7:47 am
You should not try to restore system databases unless there is a crisis.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply