Restoring SQL Server 2005 database onto SQL Server 2000

[StevenK]

We have a current client who has recently upgraded to SQL Server 2005 for one of their database applications.
I am currently running SQL Server 2000 (Service Pack 3a) on my development laptop.

We have run a backup of the SQL Server database from the client's server (now using the SQL Server Management Suite - in place of the SQL Server Entreprise Manager).
When I try and restore this onto my SQL Server 2000 (laptop) I first create an empty database (with the appropriate name) - and then using 'All Tasks' - 'Restore Database...' I select the 'From device' option and browse to the backup file (as retrieved from the client's SQL Server 2005). On return from the 'Choose Restore Devices' screen (following the file selection) I then go into the 'Options' tab of the 'Restore Database' dialog (in order to indicate the file location for the data / log files). As soon as I do this - I get the following message:

Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 3205: Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE FILELIST is terminating abnormally.

Clearly this will be due to the fact that I'm trying to restore onto an earlier version of SQL Server (trying to restore onto SQL Server 2000 from a backup taken from SQL Server 2005).

Is there any way in which I can restore from this backup file onto my SQL Server 2000?
Or is there some other means of taking a backup file from the SQL Server 2005 edition such that I can restore it as required?

 

[ptheriault]

I've never tried this and I'm not sure it will work. 2005 databases are very different from 2000. The system tables are now in the resourcedb. However, you could try to set the combatability mode for the database on the 2005 server to 8.0, then take a backup and try to restore it. You can change the db compatability from db -> properties.

- Paul
- Database performance looks fine, it must be the Network!

 

[StevenK]

How do I go about changing the compatability level (for the SQL Server 2005 database) to allow the backup taken thereafter to be restored on the SQL Server 2000 server?
Thanks in advance.
Steve

 

[ptheriault]

Rigth click on the db and select properties.
On the options page there is acompatibility level option on the top part of the page.

I did a little research on this problem this past weekend and I don't believe this is going to work. If this database isn't to large you would have better luck if you used DTS from the 2000 server to import the objects.


- Paul
- Database performance looks fine, it must be the Network!

 

[StevenK]

If we change the compatabilty level on the client's SQL Server 2005 - what impact will this have for them?

The client's SQL Server (server) is on a different site to our own.
We tend (with other clients) to take a backup through SQL Server Enterprise Manager (from client's server) and then restore the backup file to our own SQL Server (2000 in this case).
As such using DTS isn't do-able...

Thanks for the pointers so far.
Steve

 

[ptheriault]

If they are using any TSQL specific to SQL 2005 it will not work. For example varchar(max) or BEGIN TRY...END TRY..

- Paul
- Database performance looks fine, it must be the Network!

 

[Jamfool]

you can not restore a sql 2005 database to a sql 2000 installation. you could copy the objects out or script the objects(without data)

or download sql express 2005(free version) for your laptop...or buy sql 2005 developer edition which is about £30.

 

[StevenK]

We're simply trying to restore the SQL Server database file.
We're doing this by backing up the database from the client's server (SQL Server 2005) and then attempting to restore it onto one of our servers (SQL Server 2000).

Where would the TSQL specific to SQL 2005 come into play?
I'm confused by this...

Steve

 

[ptheriault]

If there are specific 2005 field types like varchar(max) you won't be able to change the compatbility level, it will fail. Also if a user has a stored proc or code in the app the uses 2005 TSQL it would fail when executed.

However, I have just found for certian that you will not be able to restore a 2005 db no matter what the compatibility level to a 2000 db.
So you have two options.
1 Use DTS to import the database to 2000
or
2 as Jamfool suggests install express. If the db is larger than 4 GB you will have to purchase developer edition as express does not support databass over 4 gb

- Paul
- Database performance looks fine, it must be the Network!

 

[StevenK]

We'll look at downloading the SQL Express 2005 from Microsoft in which case.

Will this run ok if we have an existing copy of SQL Server 2000 running on the same machine?
[We can't afford to uninstall SQL Server 2000 on these machines currently]

If we get SQL Express 2005 running successfully - is it then just a case of retoring the database backup (as taken from the client's SQL Server 2005 instance) onto this SQL Express 2005?

Thanks again.
Steve

 

[ptheriault]

SQL2K5 will not effact you 2000 instance. I will run completely separate. It will also install the new tool set leaving the 2000 tools in place. So yes once you get 2005 installed just restore your backup.

- Paul
- Database performance looks fine, it must be the Network!

 

[daveroam]

I tried putting SQL Server 2005 onto the same machine as SQL Server 2000. When I removed SQL Server 2005 it left EM inoperable, had to install SP4 to get it working again, and now it seems SQL Server 2005 attached itself to the data and log files SQL Server 2000 used (It didn't ask me or warn me) and somehow altered them, as I cannot connect to the original SQL Server 2000 database.

My experience may be unusual but still should make people wary. I ended up installing SQL Server 2005 on a different machine to isolate it. I have a separate post on trying to recover my data SQL server 2000 database.

Thx.

David

 

[ptheriault]

dave,
I've never seen that happen. You 2005 install should have created a mssql.1 folder for you new instance of sql server. Unless you changed the default install path?

- Paul
- Database performance looks fine, it must be the Network!