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 (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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 (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214550 Visits: 46268
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
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4042 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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13393 Visits: 3893
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 (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2670 Visits: 1540
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 (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 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