Backup & restore of SQL DB from one location to another - how do I get the versions of the SQL DB to be the same?

Question

Hello,

We want to restore a SQL DB from one SQL server to another from a backup. I want to be clear I don't know much about SQL - I usually install them and someone else does any programming needed. I can't even run a query unless someone walks me through it. Both servers have SQL 2008 R2 SP3 installed. HOWEVER, I notice when I open SQL Server Management Studio the top line showing the server name/instance it says SQL Server 9.0.4035 on one and SQL Server 10.50.6550 on the original server. I'm guessing that is why whenever I attempt a backup and restore of a db when I get
The media on device was incorrectly formed. SQL server cannot process the media family. Restore Headeronly is terminating abnormally. MS SQL Server Error 3241

I'm performing the backup on the original server by going to sql server management studio, right clicking on the db I want, tasks, backup - to file full backup.
I'm performing the restore on the new server by going to sql server management studio, right click on databases, restore database, to database - give it the same name or a new one - same results - 3421.

Answer 1

Both servers have SQL 2008 R2 SP3 installed. HOWEVER,

No, I'm afraid not. You say that the source server is 10.50.6550, which means SQL server 2008 R2. But the destination server is 9.0.4035, which is SQL Server 2005. And you cannot restore a backup on a lower version of SQL Server. There is no way around that.

So either you get yourself a newer version of SQL Server on the destination server or a differens means to get the data from the source to the destination server. Considering that these SQL Server has been out of support for a while, you might want to take this to a higher level and consider whether your environment in general should be on something which is supported.

SQL Server version list: https://buildnumbers.wordpress.com/sqlserver/

Answer 2

Hi,

>SQL Server 9.0.4035

This means that the version of this SQL Server instance is SQL Server 2005. And the SQL Server 10.50.6550 shows that this instance version is SQL Server 2008 R2.

SQL Server does not support directly restore higher version SQL Server backups on lower version, so you cannot restore the SQL Server 2008 R2 backups on 2005.

But you can use another method--Generate Scripts, please refer to the link below:
How to restore SQL Server 2008 database to SQL Server 2005

===============================================
If the response helped, do "Accept Answer" and upvote it.

Answer 3

Please see:

https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an

Answer 4

Thanks - I uninstalled and installed 2008 R2 db and it let me restore!