move Master database from the 2000 to 2005 SQL server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » move Master database from the 2000 to 2005...

move Master database from the 2000 to 2005 SQL server

Rate Topic

Display Mode

Topic Options

Author

Message

prasadrandi

Posted Saturday, May 09, 2009 12:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 2, Visits: 64

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

GilaMonster

Posted Saturday, May 09, 2009 2:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

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

maechismo_8514

Posted Sunday, May 10, 2009 2:44 AM

Ten 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

EdVassie

Posted Monday, May 11, 2009 2:27 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:57 AM
Points: 2,619, Visits: 2,749

Take Gila's advice. If you do this you will break SQL Server.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,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

mitra.zaimi

Posted Thursday, July 01, 2010 4:24 PM

Forum 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

Leo.Miller

Posted Thursday, July 01, 2010 10:31 PM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:44 PM
Points: 442, Visits: 1,300

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

mitra.zaimi

Posted Thursday, July 01, 2010 11:30 PM

Forum 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

alokmishra0

Posted Friday, July 02, 2010 1:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:46 AM
Points: 4, Visits: 141

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

Group: General Forum Members
Last Login: Thursday, April 11, 2013 12:58 AM
Points: 174, Visits: 555

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 »

Permissions