November 17, 2010 at 10:09 am
I need to move all the databases on a sql server 2005 server to a new computer.
What is the best way to do the restore?
To use backup/restore or use detach/attach?
Shall I restore system databases like: mster and msdb,
or instead of restoring system databases, I should do transfer logins, and tranfer jobs.
I also have reporting database for reporting services, can I restore them too?
Thanks a lot.
November 17, 2010 at 10:24 am
It all depends on the complexity of your sql instance.
Are you installing the new server baring the same name ?
Are you also migrating to a newer version of SQLServer ?
If you start with a fresh sql instance, you need to migrate the sqlusers ( include SID for the easiest way ) and port the windows accounts that have been granted on your original instance.
Then it is up to you if you want to user backup/restore or detach/attach.
Keep in mind you may also need to migrate sqlagent jobs and other stuff like alerts, queues, ...
If you new server doesn't have the same name as the old one, you may need to execute the "rename server" procedure for your instance, if you started with a restore of master, ...
http://msdn.microsoft.com/en-us/library/ms143799%28v=SQL.90%29.aspx
You'll also have to redirect applications.
For the lesser down time, maybe set up mirrored databases for all your non-system db.
If you follow this path, you must modify all connectionstrings of all applications, so they support failover partners. A witness instance will perform the switch for you once you shut down the old instance.
You will have to take care of sqljobs anyway.
If you didn't port the sid for the sqlusers, you'll also have to sync your db-sqlusers with the instances sqlusers.
sp_change_users_login @Action = 'Update_One' ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 17, 2010 at 10:41 am
We will restore sql 2005 to another new sql 2005 server.
We plan to name the new computer something else first, then after restore, turn off old server, and rename the new server name as the old one.
And please see my first posting again, my question is : shall I restore system databases or only restore user databases, and do tranfer logins and jobs?
Which is better option, and also shall I do detach/attach or do back up/restore?
November 17, 2010 at 12:14 pm
Ann Cao (11/17/2010)
We will restore sql 2005 to another new sql 2005 server.We plan to name the new computer something else first, then after restore, turn off old server, and rename the new server name as the old one.
Okay, you will still need to change it in sql server as per http://msdn.microsoft.com/en-us/library/ms143799%28v=SQL.90%29.aspx, as ALZDBA pointed out.
And please see my first posting again, my question is : shall I restore system databases or only restore user databases, and do tranfer logins and jobs?
If you restore all databases: this will keep all users, history, SSIS packages saved in MSDB, etc.
If you restore just the user databases, you will need to re-deploy all SSIS packages, build all users (check out the SSIS Transfer User task - and include the SID!) or google sp_help_revlogin for scripting out users with hashed passwords.
Which is better option, and also shall I do detach/attach or do back up/restore?
Definitely do a backup before you start.
If the database(s) are on a SAN drive, you might want to try detaching, and moving the SAN to the new server, then attaching.
Either detach/attach or backup/restore is a viable way of doing it, and it's up to you. I would do the backup\restore just to keep the one system up (even if not being used) until the new system is ready. If there is anything that would keep the database from being brought back online, detaching it might prevent you from reattaching it on the server if necessary.
If you are using SAN drives, one thing that you can do that would make something like this in the future go even easier would be to install it as a one-node cluster. When you want to move to new hardware, join the new server to the cluster. Then fail the cluster over to the new hardware, remove the old server from the cluster, and shut it down. No transferring of databases. No worries about computer names.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 17, 2010 at 12:45 pm
Thank you, that's helpful.
But I don't have any ssis packages on the server, but I do have some maintainance plan and some application jobs in sql server jobs.
I see an microsoft knowledge based instruction of how to backup/restore user databases to a new server, and then transfer logins and sql jobs.
But some one mentioned I should do a master and msdb restore. I don't know which one is a cleaner and better and easy solution.
And I don't actually find a good knowlege base article for restoring master and msdb database to a new server.
November 17, 2010 at 1:38 pm
Ann Cao (11/17/2010)
Thank you, that's helpful.But I don't have any ssis packages on the server, but I do have some maintainance plan and some application jobs in sql server jobs.
Since you're not using SSIS jobs, I'd suggest just scripting out the jobs. Then don't worry about the MSDB database. Just run the script on the new server to rebuild all of your jobs. Manually redo your Maintenance Plan - is it just database backups?
But some one mentioned I should do a master and msdb restore. I don't know which one is a cleaner and better and easy solution.
It would be cleaner to not restore these databases - they are so critical.
And I don't actually find a good knowlege base article for restoring master and msdb database to a new server.
see this KB article
Hope this helps!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 17, 2010 at 2:56 pm
Thank you, this is very helpful and clear instructions.
I will give a try.
November 17, 2010 at 3:11 pm
Ann Cao (11/17/2010)
I see an microsoft knowledge based instruction of how to backup/restore user databases to a new server, and then transfer logins and sql jobs.But some one mentioned I should do a master and msdb restore. I don't know which one is a cleaner and better and easy solution.
You can restore master and msdb, but if you are only doing it for the logins and a few jobs,the work and risk isn't worth the effort. To restore master you need to be 100% sure you have the same install and patch levels on the old and new servers, need to bring SQL Server online with the correct switches to allow a mater restore etc.
You can script all jobs to transfer to the new server, plus you can go to this site (http://support.microsoft.com/kb/246133) to learn how to script all the logins with their SIDS and password hash's, so you don't need to manualy add each login, or remap the login sids to the db users.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
November 22, 2010 at 8:37 am
Thank you, that's what I think too.
I will do the restore for user databases, then script out the logins and jobs.
The other difference for both the server, is the old one is a physical server, the new one we are going to do is a virtual server.
November 23, 2010 at 12:21 pm
Ann Cao (11/22/2010)
Thank you, that's what I think too.I will do the restore for user databases, then script out the logins and jobs.
The other difference for both the server, is the old one is a physical server, the new one we are going to do is a virtual server.
Then, would it it not be wiser and faster to stop sqlservices on the physical server
shutdown the server
cold clone the physical server to virtual machine
start up the new virtual machine witch will be identical to the old physical server .?
I have migrate several physical to virtual servers this way, including one sqlserver 2005,
Did not encountered any problem with it.
Wkr,
Eddy
November 23, 2010 at 12:40 pm
Sorry, what does cold clone mean?
The current production server is a physical server, the new one we are going to setup is a virtual one.
Can we still do the clone?
I have little knowlege about virtual machine, I will ask our network guy to see if he can do this.
Thanks
November 24, 2010 at 10:47 am
cold cloning means, cloning teh server with a bootable disc
so there is no OS nor database running while the cloning is in progress,
Once completed, this will make a Virtual machine identical as your physical machine
then you can boot up the virtual machine and have everything as it was on the new virtual servers without the headache of having to reconfigure it al again..
Wkr,
Eddy
November 24, 2010 at 10:54 am
Thanks, is that same as P(physical) to V(virtual)?
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply