HomeSQL ServerRestore SQL Server Backup file to Older SQL Server Version

Restore SQL Server Backup file to Older SQL Server Version

Recently I had to restore a SQL Server 2008 R2 Database to a Database in another machine and I ended up getting the message

“The Database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running 10.00.1600” .

Then when exploring the cause of the reason ,I found that the database that i took the backup was from SQL Server 2008 R2 .

It was the same Backup file that was used to restore in another machine and interestingly , the other machine had SQL Server 2008 .

The Version 10.50 is SQL Server 2008 R2 whereas 10.00 is SQL Server 2008.

Also , the same SQL Server Management Studio 2008 was used to access both the server instances ( 2008 and 2008 R2 ) .

It was a bit confusing for me since i was able to access the SQL Server 2008 R2 Express from the SQL Server Management Studio 2008 , but was unable to restore it to the SQL Server 2008 Express ,but then realized that since the on-disk format is different the versions and the restoring the SQL Server 2008 database to SQL Server 2008 R2 is possible . 🙂

I also had another option to generate the Create SQL Scripts and execute it in SQL Server 2008 and 2005 and it worked fine too 🙂

To find the version of Microsoft SQL Server 2008 , connect to SQL Server 2008 by using SQL Server Management Studio and execute the query

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), 
SERVERPROPERTY ('edition')

Here’s a nice reference of the list of SQL Server versions

Did you know the codename of SQL Server 2008 R2 ?? .Its “Kilimanjaro” – I remember this word when ever Rajnikanth dances for  the song “Kilimanjaro” in Robot /Endhiran (lol).

    3 Comments

  1. K Wells
    January 26, 2012
    Reply

    What does this mean:
    “but then realised that since the on-disk format is different the versions and the restoring the SQL Server 2008 database to SQL Server 2008 R2 is possible ”

    I can’t make sense of it.

    I need to back up from R2 and restore to 2008 (not R2).

  2. User Avatar
    February 16, 2012
    Reply

    This means that , you can restore the Backup file (2008) to 2008 R2 Server …

Leave a Reply

You May Also Like

When dealing with a relational database management system (RDBMS) like SQL Server, compatibility level is an important concept to understand....
In this blog post, let’s learn about the error message “49975 – Unable to load controller client certificate due to...
In this blog post, let’s learn about the error message “49973 – Cannot remove tempdb remote file to local tempdb...