Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

move Master database from the 2000 to 2005 SQL server Expand / Collapse
Author
Message
Posted Saturday, May 09, 2009 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:59 AM
Points: 2, Visits: 84
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.
Post #713472
Posted Saturday, May 09, 2009 2:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #713489
Posted Sunday, May 10, 2009 2:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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.

Post #713669
Posted Monday, May 11, 2009 2:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:21 AM
Points: 2,797, Visits: 3,079
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 2014, 2012, 2008 R2, 2008 and 2005. 25 March 2014: now over 28,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #713917
Posted Thursday, July 01, 2010 4:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 9:17 PM
Points: 2, 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
Post #946526
Posted Thursday, July 01, 2010 10:31 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 7:44 PM
Points: 446, Visits: 1,314
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
Post #946580
Posted Thursday, July 01, 2010 11:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 9:17 PM
Points: 2, 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?
Post #946588
Posted Friday, July 02, 2010 1:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 5:45 AM
Points: 4, Visits: 201
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
Post #946613
Posted Friday, July 02, 2010 1:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:04 PM
Points: 176, Visits: 563
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.
Post #946623
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse