Restore SQL Server 2000 Database to SQL Server 2008

[bret_miller]

In 2005, I backed up a SQL Server 2000 database with a prior version of Backup Exec. Since then SQL Server was upgraded to 2005, then to 2008, and Backup Exec was upgraded to 2012 with several interim versions I'm sure. So I still have my backup tape with this backup on it, but now when I try to restore it, I get the following error:

The job failed with the following error: The selected database appears to have been backed up from a newer version of SQL Server.  Backups made from newer versions of SQL Server cannot be restored to older versions of SQL Server.

But it's not from a newer version of SQL Server, it's from an old version.

So now what? What's the point of backups if you can't restore old ones?

[CraigV]

...I think this is more of a Microsoft question than a BE question. If should be a question for a Microsoft forum...if you had the same version of SQL, and as long as it was supported with your version of BE, the restore should run.

Fact of the matter is that you're trying to restore a very old version of SQL to a much newer version, and like trying to restore Exchange 2003 data into Exchange 2012 isn't supported, your situation is also probably not.

Best to try restore the DB to a server running SQL 2000 and do what you need to there.

Thanks!

[bret_miller]

Perhaps if I were trying to restore a Microsoft SQL Server backup file. But the backup was done via Symantec's Agent for SQL Server. As far as I know, there is no way to restore it to a backup file that can be restored manually. And since Backup Exec is not passing back a SQL Server error message or code, how can Microsoft determine what might be causing this error. Maybe using Backup Exec's agent is not a good thing...

I'll see what I can do about finding an older version of SQL Server and a server to install it on to restore this one database if Backup Exec 2012 will even do it considering it seems to think it's NEWER than 2008...

[pkh]

It does not matter whether the backup is done using SQL Studio or the BE SQL Agent.  The backup is the same.  The BE SQL agent invokes the SQL API to do the backup.

Your problem is that you are trying to restore a SQL 2000 database to SQL 2008.  This is not possible from a Microsoft standpoint.  It is like trying to do a database upgrade by do a backup and restore.  This is not a BE limitation, but a Microsoft one.

[bret_miller]

OK... so I probably should change the title on this, but since it's still the same problem... I set up a new Windows Server 2003 server and installed SQL Server 2000 on it. I applied SP4 and applied all the patches for it. After all, I don't know of any way to know for sure the exact build I had installed on January 1, 2005. Regardless of that, I really doubt that Microsoft would disallow a restore from SQL Server 2000 SP3 to SQL Server 2000 SP4.

But I am still getting, "The job failed with the following error: The selected database appears to have been backed up from a newer version of SQL Server.  Backups made from newer versions of SQL Server cannot be restored to older versions of SQL Server."

So I'm thinking that because the backup is old, Backup Exec 2012 isn't reading the SQL Server version correctly from the backup and is simply refusing to try to restore it.

Any ideas on how to get around it?

[pkh]

I had seen a Symantec document which states that to restore a SQL database it must be restored the same version and patch version.  However, I cannot find that document at the moment.  This requirement makes sense in that when you apply a SP, it does upgrade the database.  I know this is no comfort to you, but you can confirm the restoration requirement with Microsoft.  BE calls the SQL API to do the backup and restores.  If the API refuses to do the restore, there is nothing much that BE can do.

[pkh]

I found this document.  For your restore to the SQL Server 2000, maybe you are experiencing this problem

http://www.symantec.com/docs/TECH209705

You can download BE 2010 R3 from 

http://trialware.norton.com/files/fc/Backup_Exec_2010_13.0.5204_MultiPlatforms_Multilingual_DVD.iso

install it and give it a go.

Note that even with BE 2010, you cannot restore a SQL 2000 backup to SQL 2008.

[bret_miller]

That sounds exactly like the problem. I will try the solution and report back. 

[bret_miller]

And now that I'm thinking about this, there probably isn't a way to install 2 versions on the same server, right? And since I have only 1 backup server and 1 tape library, I'm hosed. I'd have to have at least a second server and compatible tape drive to restore this backup unless Symantec gets around to fixing Backup Exec 2012 to be backward compatible.

Still wishing I'd never upgraded to version 2012. Had to work around too many issues. And now it appears I have an issue that I can't even work around.

Thankfully this isn't a critical restore. But my bosses aren't going to like finding out that we have to invest in hardware to restore an older database if we ever really need it. Automation is nice, but only when it doesn't screw you in the future, which this time it has.

[pkh · Accepted solution]

You can duplicate the tape backup onto disk so you do not need another tape drive.  The media server requirements for BE 2010 is not very much.  You can load both the SQL 2000 and BE on the same machine, restore whatever you want on this machine and then release the machine.

[bret_miller]

Thank you. A lot of work for a restore, but after installing Windows Server 2003, SQL Server 2000, and Backup Exec 2010 R3 in trial mode, I was able to duplicate the tape backup to a disk folder on the new server, import and catalog it into Backup Exec 2010 (which surprised me that it would read a backup file from a newer version), and restore it. Not trivial, but doable when really necessary.

[CraigV]

There should be no problems really reading *.bkf files written by a newer version of BE. That file format is a standard, and I shouldn't change at all.

Thanks!