January 22, 2009 at 7:04 am
I am looking for a straight forward way to move a SQL Server 2005 to a new Windows server with a different name. I had thought of doing the new install and restoring the master database to the new server the attaching the database, this way I can keep the usernames and passwords intact. The only problem that I can see is that the directory structure will be different and after the master restore is complete it will not know where to look for the files. Back in 2000 you could do a master restore and then change the startup parameters to the new location. I tried this in a test lab and was unsuccessful.
any ideas ????
Aaron
Aaron
MCITP: Database Administrator, MCITP: Server Administrator, MCTS: Windows Server 2008 Active Directory – Configuration, MCTS: Windows Server 2008 Network Infrastructure – Configuration, MCTS: Windows Server 2008 Application Platform – Configuration, MCTS: SQL Server 2005, MCTS: Microsoft Exchange Server 2007 – Configuration, MCSE 2003, MCSA 2003, MCDBA, Security+, CCNA
https://www.mcpvirtualbusinesscard.com/VBCServer/AaronChristenson/interactivecard
January 22, 2009 at 8:36 am
I usually take this approach for moving databases. I never move entire instances and mess with restore system databases because of the risk of instability.
> change db's to single user mode & backup to disk
> copy to new server and restore to new location
> run dbcc checkdb
> transfer logins with sp_help_revlogin (http://support.microsoft.com/kb/918992)
> change db to multiuser mode
If needed
> transfer ssis jobs & sql agent jobs
January 22, 2009 at 8:49 am
That would work, but I can forsee a problem, what about all the object permissions. I will have to run a test of attaching the databases to a new server and running this create user script and see if the users are created with the same database user mappings.
Aaron
Aaron
MCITP: Database Administrator, MCITP: Server Administrator, MCTS: Windows Server 2008 Active Directory – Configuration, MCTS: Windows Server 2008 Network Infrastructure – Configuration, MCTS: Windows Server 2008 Application Platform – Configuration, MCTS: SQL Server 2005, MCTS: Microsoft Exchange Server 2007 – Configuration, MCSE 2003, MCSA 2003, MCDBA, Security+, CCNA
https://www.mcpvirtualbusinesscard.com/VBCServer/AaronChristenson/interactivecard
January 22, 2009 at 8:56 am
They will have the same permissions because the sp_help_revlogin script creates the accounts with the same SID & password. SQL accounts will map properly, and domain accounts have a domain SID - so they will be the same as well.
Note-
if you want to make sure, you can use the sp_fixusers script as well
January 22, 2009 at 9:01 am
I ran a quick test and you are correct the permissions transfered correctly. I think I will go with this plan.
1. Run transfer user scripts
2. Detatch all databases
3. copy to new location
4. attach databases to new sql server
5. run login script
this will be a lot easier that restoring the master database and then tring to figure out the mess that creates.
thanks for you help
Aaron
Aaron
MCITP: Database Administrator, MCITP: Server Administrator, MCTS: Windows Server 2008 Active Directory – Configuration, MCTS: Windows Server 2008 Network Infrastructure – Configuration, MCTS: Windows Server 2008 Application Platform – Configuration, MCTS: SQL Server 2005, MCTS: Microsoft Exchange Server 2007 – Configuration, MCSE 2003, MCSA 2003, MCDBA, Security+, CCNA
https://www.mcpvirtualbusinesscard.com/VBCServer/AaronChristenson/interactivecard
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply