October 29, 2009 at 9:04 am
I was wondering if anyone could help me understand the differences between user databases and system databases, as it would relate to a server migration.
I have Googled the topic for 2 days and have yet to find a thread or article, that gives me alot of detail on this process.
I am interning and they are doing a server migration SQL2005 - to SQL2005 same OS.
I have read quite a bit, but still looking for more info.
Any feedback would be quite appreciated.
Thanks in advance.
October 29, 2009 at 9:17 am
I am interning and they are doing a server migration SQL2005 - to SQL2005 same OS.
Thanks in advance.
You mentioned you are Interning and your company is doing a server Migration SQL2005 - to SQL2005 same OS, have they given any task to you that you wanted to perform and need help?
Migration from SQL 2005 to SQL 2005 same OS, if So they are sticking to SQL 2005 o the same OS are they upgrading to better Hardware then? what going on?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 29, 2009 at 9:22 am
Oh sorry, yes they are upgrading to better hardware more ram, processing power.
And i am trying to gather a list of steps and tasks to accomplish completely moving the entire DB from one instance to another, including all jobs, SP's, security, users ect...
October 29, 2009 at 9:33 am
user databases, detach and attach.
For logins, you want to use sp_help_revlogin to script out logins and move them to the new instance.
I haven't tried the tasks to move jobs/alerts/etc from msdb, but if you are moving to the same paths, I'd look at restoring mdsb from the current instance to the new one.
October 29, 2009 at 9:33 am
You can find some of the steps to do in one my recent Blog
Downgrading SQL Server Enterprise Edition to Standard Edition
Although you are not doing the same thing, but you can follow most of the steps from that Blog.
Remember to have a way to fallback onto the old System with a good backups of all the current Databases (User DB's MSDB and Model)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 29, 2009 at 11:08 am
with user databases attach and detach but what about system databases is their a different technique that needs to be applied?
October 29, 2009 at 11:19 am
You can restore the System databases MSDB and Model, try finding a good blog on that.
What I achieved was actually a bit more adventurous, I stopped SQL Server and replaced the MSDB and Model Databases with the copies from the current System. and restart the SQL Instance.
Try doing restore cause you would find many Blogs and help on those steps.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 29, 2009 at 11:36 am
with SQL 2005 I would be careful about restoring the msdb database to a different server. In SQL 2000 this was a doddle you just updated the sysjobs.originating_server column to the new server name and that was it. There is no equivalent in SQL2005 and you can find you cannot edit maintenance plans for example, and if you delete them you actually update the original server.
If your msdb is simple in that it contains few jobs and few SSIS packages I would script out the jobs from SSMS and load them in to the new server, copy over the SSIS packages using 'save as' to the new server and recreate maintenance plans from scratch.
Unless you have made changes to the model database on your old server, no need to copy that over.
IF you are renaming your new server to the same as the old one at the end of the process you can get away with restoring the msdb from the old server, or sliding the files into place as was suggested above.
---------------------------------------------------------------------
October 29, 2009 at 1:14 pm
I just wanted to thank everyone for their response, it has helped me a great deal in understanding the steps and tasks involved in database migration. I believe the system restore topic, answers my question on how to move an instance of SQL 2005's system database between systems.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply