Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

SQL Server Migration 2000 - 2008

Hi

I've just been presented with a Microsoft SQL Server 2000 which has a few smallish databases on it that I need to move to SQL 2008 at least.  I don't want to perform an in place upgrade on that server as it's old and given it's the production machine it would be too stressful,  I do have a spare server where I could install SQL2000, copy the production data to and perform an in place upgrade on that.

What I was really hoping for was for any advice on best practice from someone that has done something similar, I am of course concerned about migrating the DTS jobs as well.

Many thanks

Ed
0
edwalters
Asked:
edwalters
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you have a new server you don't need to install the SQL Server 2000 there. You can immediately start installing SQL Server 2008R2 plus the last SP and CU. Then you just need to backup the SQL Server 2000 databases and restore them on the new SQL Server 2008R2 and change their compatibility mode.
Another option for the backup/restore method is to detach/copy/attach the databases from old server to the new one. Again at the end of process you need to change their compatibility mode.
0
 
edwaltersAuthor Commented:
Thanks for that, that would be simple, what would happen to the DTS jobs?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's the hard job. Unless the DTS are very simple Import/Export Data you'll need to redesign them in SSIS (Integration Services).
In alternative you can install the DTS runtime for SQL Server 2008, which works on 32-bit platforms only.
Try to run the DTS Package Migration Wizard to see how many of the DTS you can migrate automatically.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now