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


System databases


System databases

Author
Message
middletree
middletree
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 195
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?
oded.raz
oded.raz
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 31
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
MarkusB
MarkusB
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5899 Visits: 4208
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.

Markus Bohse
middletree
middletree
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 195
Both of you: thanks.

Markus: do you know of any tutorials to do what you describe?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64053 Visits: 19117
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
middletree
middletree
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 195
will give it a shot.

thanks
princess.lipscomb
princess.lipscomb
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 169
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?
princess.lipscomb
princess.lipscomb
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 169
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
middletree
middletree
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 195
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.
laaj235
laaj235
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: 16
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
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