SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


move Master database from the 2000 to 2005 SQL server


move Master database from the 2000 to 2005 SQL server

Author
Message
prasadrandi
prasadrandi
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 122
hi,

I am trying to move Master database from the 2000 to 2005.

without stop the production server is it possible or not?

if possible give me some guidance


Ramaprasad.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)SSC Guru (548K reputation)

Group: General Forum Members
Points: 548165 Visits: 47738
Don't try. The system databases shouldn't be moved from one version to another. It's really asking for problems.

Script out whatever it is in the master database that you want (logins, roles, server permissions, linked servers) and then run those scripts against the 2005 server. Same with msdb.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


maechismo_8514
maechismo_8514
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9786 Visits: 2228
prasadrandi (5/9/2009)
hi,

I am trying to move Master database from the 2000 to 2005.

without stop the production server is it possible or not?

if possible give me some guidance


Ramaprasad.


But why want to do that?, IF it is for logins follows gilas method.
EdVassie
EdVassie
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34333 Visits: 3932
Take Gila's advice. If you do this you will break SQL Server.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
mitra.zaimi
mitra.zaimi
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 5
would you send me a link to show how to script against master database ( in sql 2000 ) to collect data and then restore it to sql 2005 or sql 2008
Leo.Miller
Leo.Miller
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7144 Visits: 1568
mitra.zaimi (7/1/2010)
would you send me a link to show how to script against master database ( in sql 2000 ) to collect data and then restore it to sql 2005 or sql 2008


Errrr... Links to script? Script data?

To script most objects in SQL 2000 you can Use Enterprise Manager, Right Click and select generate script.

However if you are asking questions like this I would seriously consider asking someone with more experience to migrate the system from SQL 2000 to SQL 2005 or 2008. This is not really a task for the faint hearted. A number of features are being depricated and you could end up with serious issues.
Have you at least used the Upgrade Advisor to check that the databases can actually be upgraded?

Also do you want objects or data? Besides things like logins, linked servers and the like, you shouldn't have anything in Master that you need to migrate to the new server.

Leo Miller
mitra.zaimi
mitra.zaimi
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 5
Thanks.
what we have 2000 environment and want to move all databases ( system and data ) to 2005 environment.
I know that data databases with detach and attach, but for system databases ( master and msdb ) was not sure as we need to also move all logins, permissions, rolls, DTS ,.. which are in master and msdb.
can I script msdb the same way as for master?
alokmishra0
alokmishra0
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 229
u can move the login from one instance to another by executing execi sp_help_revlogin SP, followed by the sp_hexidecimal SP.
it ll move all the login from one server to target server.
U can find enitre information regarding these two stored procedure on this article posted on this link..
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133.

Hope this will help you
Open Minded
Open Minded
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 567
You can use the SQL Server Business Intelligence Studio to migrate the DTS packages. Create a new project, right-click SSIS Packages then "Migrate DTS 2000 Package".

Like the others, I suggest you do not bring over the MS2000 databases. Look up "What's New in SQL Server 2005" for the 1,001 items which in my opinion, will cause your server to crash if MS 2000 sys tables are forced unto it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search