2

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

Improve this question

5 Answers 5

Reset to default
3

Thought of using virtualisation?

Improve this answer
3
  • 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, 2009 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, 2009 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, 2009 at 12:25
0

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.

Improve this answer
2
  • 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, 2009 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.
    – DCNYAM
    Oct 5, 2009 at 14:27
0

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.

Improve this answer
3
  • 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, 2009 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, 2009 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, 2009 at 19:14
0

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.

Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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