February 10, 2008 at 11:19 pm
Hello Everyone,
I always had a good feedback from this site. I hope the same this time.
We are upgrading sql server 2000 to sql server 2005 using detach and attaching feature . Now I have couple of questions, please suggest me.
1. There are quiet a few tables and proc's added to system databases in sql 2000 in last few years. So how do I get them to sql server 2005 system databases. I don't know how many changes are made to sql 2000 system databases. Can we take a backup of sql server 2000 system databases and restore them on to the sql server 2005 system databases? How do we deal with system databases in upgrade process?
2. There are more than 100 scheduled jobs in sql server 2000, is there any way I can transfer them to sql server 2005, rather than scripting every job.
February 11, 2008 at 4:33 am
1. You cannot restore system databases from SQL 2000 to SQL 2005 as both are of different builds. So the only option is to script all the logins using http://support.microsoft.com/kb/246133
using the method 2 in the above link.
2. For Jobs you can script it or else I think you can make use of Transfer Job task in SSIS http://msdn2.microsoft.com/en-us/library/ms137568.aspx
I am not too sure about it may someone can confirm if that it possible 😉
[font="Verdana"]- Deepak[/font]
February 11, 2008 at 10:22 am
Also, afterwards you can script out the tables you've added to your SQL 2000 system databases and recreate them in a new user database on SQL 2005. You can then set up generic synonyms to these objects so you can query against them as though they're local in all your databases. They are not a part of your master or msdb databases any longer, but they can behave as such via the synonym.
These tables can be populated via SSIS or by creating a linked server and using TSQL to run INSERT statements against the new tables in SQL 2005. If you want to preserve any identity values in these tables do not forget to SET IDENTITY_INSERT ON|OFF before and after each table insert.
It's always worthwhile to have some instance of a "tools" or "scripts" database on your SQL instances so that you can run stored procs or collect instance metadata without altering the system databases in any manner.
You can still query metadata from the system tables using compatibility views, but the underlying structure of the master and msdb tables has changed.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 12, 2008 at 2:50 am
You may find http://www.codeplex.com/SQLServerFineBuild useful. I published this to help provide a 1-click install and configuration of SQL Server 2005. It includes a section on upgrading from SQL Server 2000, and some SPs to script out the objects you need to move to SQL Server 2005.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply