Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm in the process of migrating development databases from an old (soon to be decommissioned) database server to a VMWare-hosted virtual machine running Windows 2008 x64.

The old server runs SQL Server 2000, SQL Server 2005 and SQL Server 2008. I am aware of the "compatibility level" feature that exists within SQL 2005 and SQL 2008. One of the requirements is to be able to backup local SQL 2000 and 2005 databases and restore them on client's SQL 2000 and 2005 servers - this is something that compatibility mode doesn't appear to support (MSDN Forums: How to restore SQL 2005 DB to SQL 2000).

My questions is this: Is there a special install order I should be following?

My plan is to:

  • Install SQL 2000, patch it
  • Install SQL 2005, patch it
  • Install SQL 2008, patch it

EDIT 1: If I backup a SQL 2000 database running on SQL 2005 server in "compatibility level" I won't be able to restore it on SQL 2000 server.

EDIT 2: Reasons I am asking about install order: a) I've been burned by software installs before (namely Visual Studio); b) paranoia

EDIT 3 (FINAL): Following advice given by [Guy], I've decided to run each instance of SQL Server on a dedicated virtual machine (instead of a shared one). This should simplify maintenance and increase reliability.


Thanks,
Arnold

share|improve this question
add comment

5 Answers

up vote 3 down vote accepted

Thought of using virtualisation?

share|improve this answer
    
The new server is a virtual server. Do you mean to place each DB instance in its own VM? That is definitely a posibility but I will have to calculate the hardware and backup cost of this approach. –  Arnold Zokas Oct 5 '09 at 14:53
    
I also prefer to keep them separate if you have the means. Helps to prevent funkiness with auto-detection things like hotfix installers, and you can also stick with default instances which is sometimes easier than dealing with named instances. –  squillman Oct 5 '09 at 16:46
    
I would (and do) keep the versions separate on each server. Backup your databases to a common file share and mirror that file share (to tape, different building, off site etc). You WILL get stuck trying to backup and restore databases across versions (going down anyway). SQL Server (when on it's own) can run quite happily on a small vm (1 CPU, 1GB RAM) - Disk IO tends to be the first bottleneck. –  Guy Oct 6 '09 at 12:25
add comment

The compatibility code that the datbase is in doesn't have anything to do with which versions of the engine you can restore the database to. It only changes the commands which are available to you, and the way the engine responds to a some commands.

If simply changing the database compatibility mode from 90 to 80 would allow you to restore the database to a SQL 2000 machine it would need to rewrite the database files in the SQL 2000 format so that the SQL 2000 server could understand them.

share|improve this answer
add comment

I had to deal with a similar issue. We upgraded one of database servers to SQL2005 but still needed to send the data to a SQL2000 box. We used to use Backup and Restore functions. Now we use the Microsoft SQL Server Database Publishing Wizard to generate SQL scripts to go from 2k5 to 2k. I don't know if it works with SQL2008 or Windows 2008.

share|improve this answer
add comment

What do you mean "compatibility mode"? Do you mean "compatibility level"? If so, what level is the SQL 2000 databased set to? If it is set to SQL 2005 compatibility level then no, you won't be able to restore it to a SQL 2000 server.

share|improve this answer
    
Yes, I meant compatibility level (edited the post). And no, what you suggested is not possible. The problem is not with database level - it is with backup file format. SQL 2005 uses a backup format not compatible with SQL 2000 database. –  Arnold Zokas Oct 5 '09 at 14:59
    
I didn't suggest anything. I said that if the database is set to SQL 2005 comaptibility level, then it is not possible to restore it or attach it to a SQL 2000 server. –  joeqwerty Oct 5 '09 at 16:12
    
For clarification, it's the internal version of the database, not the compatibility level or backup file format, that prevents a database from being restored to a server of an earlier version. That internal version number indicates the internal structure of the database, whereas the compatibility level just controls the set of commands that can be used with that database for backward compatibility. –  Ed Leighton-Dick Oct 5 '09 at 19:14
add comment

Restoring forward is easy. You can take the SQL2000 databases and restore them to SQL2005 without any problems.

In terms of install order, it shouldn't matter. Each product will install independent of the others.

share|improve this answer
    
If I backup a SQL 2000 database running on SQL 2005 server in "compatibility mode" I won't be able to restore it on SQL 2000 server. –  Arnold Zokas Oct 5 '09 at 14:01
    
If you are going to have each version of SQL server installed, why would you want to go backwards? Personally, I always install the oldest version first and work forward from there. Although it should not matter, I believe newer versions of software are more likely to be aware of the older versions and not screw them up. Whereas, older versions would have no idea regarding the file structure and whatnot of newer versions. But that is just my opinion and, as you said, paranoia. –  NYSystemsAnalyst Oct 5 '09 at 14:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.