System databases

  • I started a thread a few days ago asking about copying sytems databses, such as Master, MSDB, etc., over to a new server if you are upgrading. In our case, we are moving databses from a Windows 2000 / SQL 2000 instance to a Win 2003 / SQL 2005 instance.

    Is there any reason to move, for example, the msdb?

  • From my experience - You shouldn't move any of the system databases to the new database when upgrading from 2000 to 2005.

    Oded

    www.dbsnaps.com

    www.orbiumsoftware.com

  • middletree (6/11/2009)


    Is there any reason to move, for example, the msdb?

    No. Don't move the msdb. What you should do is script out all your jobs, operators and alerts and thenm recreate them on the new server.

    [font="Verdana"]Markus Bohse[/font]

  • Both of you: thanks.

    Markus: do you know of any tutorials to do what you describe?

  • You can right click on most of the folders and there is a "Generate Scripts" option for jobs, alerts, etc. You run that, then take the result and run that in a query window on the new server.

  • will give it a shot.

    thanks

  • When you say you are moving them over. Do you mean you are just going to take the backup and restore to 2005 and leave the compatibility at 80 so that it is a 2000 DB using a 2005 capabilities?

  • I am not certain you can do this. The reason for those dbs is so very important

    Master

    Purpose - Core system database to manage the SQL Server instance. In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema. In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.

    Prominent Functionality

    Per instance configurations

    Databases residing on the instance

    Files for each database

    Logins

    Linked\Remote servers

    Endpoints

    Resource

    Purpose - The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.

    Prominent Functionality

    System object definition

    Additional Information

    Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects Prior to SQL Server 2005 the system related data was stored in the master database Read-only database that is not accessible via the SQL Server 2005 tool set The database ID for the Resource database is 32767 The Resource database does not have an entry in master.sys.databases

    Model Purpose - Template database for all user defined databases Prominent Functionality Objects Columns Users Additional Information User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases The database configurations such as the recovery model for the Model database are applied to future user defined databases

    MSDB

    Purpose - Primary database to manage the SQL Server Agent configurations Prominent Functionality SQL Server Agent Jobs, Operators and Alerts DTS Package storage in SQL Server 7.0 and 2000 SSIS Package storage in SQL Server 2005 Additional Information Provides some of the configurations for the SQL Server Agent service For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database

  • princess.lipscomb (2/2/2010)


    When you say you are moving them over. Do you mean you are just going to take the backup and restore to 2005 and leave the compatibility at 80 so that it is a 2000 DB using a 2005 capabilities?

    I have to be honest with you. My question was posted so long ago I have no idea what I wanted.

  • Greetings:

    In reference to moving from a MS SQL Server 2000 to a MS SQL Server 2005, we have done that and used a 'migration' script built into the upgrade. Remember that when moving from a MS SQL Server 2000 to 2005 changes have been made to the System (default) database. Therefore from our experience we did not move these databases to the new 2005 server. We did migrate the data from old to new database formats.

    Now we used a step by step guide produced by 'SearchSQL-

    Server.com' author Jeremy Kadlec (Edgewood Solutions). It worked perfectly.

    kr,

    Jeff

  • This is a great thread and the responders were very polite to a new DBA.

    The site rocks.

    With a Dynamics Great Plains migration I have never had to migrate the system databases. The install of the application modifies the master database but I do have to run several scripts to move over the company databases.

    I 've had no reason to migrate Master.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply