SQL Server 2000 Server Change 1

[bdc101]

We have an old server running SQL Server 2000 that we are replacing, and we are keeping SQL Server 2000 on the new server. The reporting department wants to rebuild Reporting Services 2000 on the new server instead of restoring the old copies when we do the switch. If I do not restore the msdb database on the new server is there anything I will need to rebuild other than jobs and packages? Is there a quick easy way to export the job and package data from the old server to the new server?

 

[mrdenny]

You can use a DTS package to move the jobs. Packages can be opened and resaved to the new server.

The SSRS data isn't saved in the msdb database, you should be able to restore this database without issue.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site

 

[bdc101]

Thanks for the response Denny. The reason we do not want to restore this database is there are a lot of jobs for SSRS subscriptions. We set all our reports up to be automatically emailed every morning. If we restore the msdb database all those jobs will be there, and we do not want that. I am looking at writing some queries to copy the relevant package and job data out of the tables in the existing msdb database and inserting it into the new.

 

[mrdenny]

It would be pretty easy to identify the jobs after you restore the MSDB database and delete the SSRS jobs. Sounds like you are going the hard way to clean up the jobs.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site

 

[bdc101]

I agree with you Denny, but the decision was taken out of my hands. They want the reports in Reporting Services rebuilt from scratch before we do the server switch. They want all these changes done before the server switch so it is ready before we change over. If I restore the msdb database, then I lose all the subscriptions the reporting department makes on the new server before we switch over.

 

[mrdenny]

Ah, if they are trying to pre-stage everything on the new machine then yes you'll need to copy everything over the hard way.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site