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 12»»

System databases Expand / Collapse
Author
Message
Posted Thursday, June 11, 2009 2:17 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, 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?
Post #733338
Posted Thursday, June 11, 2009 11:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 10, 2009 11:23 AM
Points: 13, 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
Post #733518
Posted Friday, June 12, 2009 1:33 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 4,432, Visits: 4,157
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
Post #733562
Posted Friday, June 12, 2009 8:05 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, Visits: 195
Both of you: thanks.

Markus: do you know of any tutorials to do what you describe?
Post #733867
Posted Friday, June 12, 2009 8:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:02 PM
Points: 32,834, Visits: 14,974
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
Post #733885
Posted Friday, June 12, 2009 8:38 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, Visits: 195
will give it a shot.

thanks
Post #733915
Posted Tuesday, February 02, 2010 12:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 14, 2011 10:13 AM
Points: 25, 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?
Post #858212
Posted Tuesday, February 02, 2010 12:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 14, 2011 10:13 AM
Points: 25, 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
Post #858219
Posted Tuesday, February 02, 2010 1:04 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, 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.
Post #858222
Posted Friday, February 26, 2010 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:02 AM
Points: 2, 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
Post #873570
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse