November 19, 2010 at 11:29 am
I have a sql server 2000 where a third party database is residing to support web order applications. I have a lot of dts packages and sql jobs running against the database. We're planning to move the sql server to a new machine with newer os. There will be no sql server upgrade. I contacted the third party tech support about the migration. They are recommending backup & restore of only that database. Of course, I know I have to move the server level objects like logins, dts, and sql jobs separately. Yes, configuration settings also. My question is why the system databases are not part of the migration. They contain very important metadata about database objects like tables, sql jobs, logins, and dts. Is it because the system databases in the new server will get updated when I restore the datase and move the server level database objects? This is the first time I'm doing the migration. So, excuse me if the question is silly.
November 19, 2010 at 12:36 pm
It is always a best practice to restore system databases like master and msdb when you are moving SQL Server instance. It also avoids recreating the logins,jobs etc. If DTS packages are not saved in File system even those will be moved to new instance when you restore the msdb database.
This article[/url] on SSC explains how to move system databases.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 19, 2010 at 3:14 pm
Oh I see. What you're saying is that if you move those system databases along with user databases, you don't have to manually move the logins, dts, and other database objects, which I thought I had to do?
November 19, 2010 at 3:33 pm
chulheekim (11/19/2010)
Oh I see. What you're saying is that if you move those system databases along with user databases, you don't have to manually move the logins, dts, and other database objects, which I thought I had to do?
Your question isn't silly, and yes, it will save you a lot of headaches about object metadata.
One thing to consider though is also a name change of the server, this may actually be two processes in one. If it is, you'll have to touch all your DTS packages for edits.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 19, 2010 at 4:41 pm
I'm sorry. But I don't quite understand what you mean by two processes in one. The new sql server will take the old one's name once the old one is turned off completely because There're a lot of integration going on between the servers and other servers must not recognize any change. Is there any other concern on moving system databases?
November 19, 2010 at 5:06 pm
chulheekim (11/19/2010)
I'm sorry. But I don't quite understand what you mean by two processes in one. The new sql server will take the old one's name once the old one is turned off completely because There're a lot of integration going on between the servers and other servers must not recognize any change. Is there any other concern on moving system databases?
Nope, if you're not changing the name and you have no IP address references you're good to go. Just wanted to make sure.
The two processes I meant was a server name change and a server migration.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 19, 2010 at 5:42 pm
Oh Okay. Thank you so much.
January 14, 2011 at 4:29 pm
For some reason, we decided to give a different name to the new server. Is it still a valid and good plan to move the user dabase and the system databases. Will the dts jobs and logins be working fine in the new environment?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply