June 11, 2009 at 2:17 pm
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?
June 11, 2009 at 11:27 pm
From my experience - You shouldn't move any of the system databases to the new database when upgrading from 2000 to 2005.
Oded
June 12, 2009 at 1:33 am
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]
June 12, 2009 at 8:05 am
Both of you: thanks.
Markus: do you know of any tutorials to do what you describe?
June 12, 2009 at 8:17 am
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.
June 12, 2009 at 8:38 am
will give it a shot.
thanks
February 2, 2010 at 12:53 pm
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?
February 2, 2010 at 12:59 pm
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
February 2, 2010 at 1:04 pm
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.
February 26, 2010 at 10:07 am
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
February 26, 2010 at 1:00 pm
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