Fix Error in SQL Server Database Backup Restore on Lower Version

Now-a-days, it has become very important to backup your data and restore it at your desired destination. So, today we will be talking about an error in SQL Server Database Backup Restore on Lower Version. This error comes when we try to restore the backed up data from higher version to a lower version.

User Query related to SQL Server Database Backup Restore

A few weeks ago, I had tried upgrading my SQL Server to some upper edition. But, on attempting for SQL Server database backup restore on MS SQL server, when I tried restoring that to my test-restoration server (where my company’s backups are checked), to my aghast, I encountered an error. The test-server had not been upgraded to this upper SQL server version, but there was nothing there as such inside my SQL database that would have been incompatible with the lower version of SQL. Seeing the prompted messages, it appeared to me that SQL Server had easily recognized the backup but while restoring it, it actually got failed.

The Fact!

There is nothing wrong that this user has been doing. It is not possible to take backups from upper SQL database server versions, and then restore those backups to lower SQL versions. This is unfortunately the truth, that in spite of the fact that SQL database Server’s Management-Studio definitely would appear for recognizing backups. If a user selects restoring this particular backup, but is in some lower SQL Server version, then an error is received.

Error Arising While Making an Attempt to Restore SQL to Lower Version

In some cases, like take for example a case in particular encountering such an error, there SQL database Server’s Management-Studio tells that SQL Server had been tried for restoring the backup, but on realizing that the backup was from MS SQL database Server 2008R2 version but was being tried to be restored to SQL database Server 2008 version resulting in failed restoration.

Reasons of Error in SQL Server Database Backup Restore on Lower Version

Even though MS SQL database Server tells much about backups as it ascertains that the right SQL Server’s version where that backup had been taken, it can still be worked for a forced-restore of the backups. Even so all database elements, including all tables, all of the views, stored procedures, as well as datatypes, etc., have total compatibility with older SQL Server’s version, the process of restoration would tend to fail at all times. This is because of the reason that the SQL database’s version consists of much more than just these components; and these consists of the changes made to databases with every SQL server’s version, and these include the cumulative-updates as well as service-packs too. For example, say for instance, if a command is run to restore any backup that was taken on MS SQL Server 2005 version to some other version say SQL 2008 Server. Then weird behavior shows up.

Way to Restore SQL Server Database Backup on Lower Version

• First of all, start SQL Server’s Management-Studio
• Then, connect to that instance where that database is residing
• After that, navigate to “Tasks” in the database-context menu
• Now, Generate Scripts….
• This invokes a wizard – “Generate & Publish Scripts”
• You have to click Next as introductory step of this wizard
• In Choose Objects wizard’s step, ensure that the option for “Script-entire-database-&-all-database-objects” has been selected
• Now, click Next
• Select the option for “Save-scripts-to-a-specific-location” in the “Set Scripting” option wizard’s step
• Then, you need to specify if database objects as well as data would get scripted to one single file, or more than one files, along with the path as well as the encoding of scripts thus generated
• Now, click Advanced
• In “Advanced-Scripting-Options” dialog that appears, you must specify SQL Server’s version where database is needed to be restored to in the option for “Script-for-Server-Version”
• In this same “Script-for-Server-Version” option, also specify “Schema & data”
• Also, you must set the options below as True:
  • Script Logins
  • Script Triggers
  • Script Full-Text Indexes
• Then, click OK
• After that, click Next
• Click Next in wizard’s Summary-step
• Then, click Finish in “Save or Publish Scripts” wizard’s step
• Finally, execute generated SQL script or scripts against that lower SQL instance

Drawbacks of Manual Method

• Won’t export all database objects (like sequences and queues etc)
• Fails if database consists of dependencies which SQL’s Server does not recognize (example dependencies to those databases that reside on the linked servers)
• The method does not work in case where access to original database is missing due to any cause (like in case database is being migrated to some off-site location that does not have network connection)

SQL Backup Recovery Tool

Does the drawback means that finally the backup won’t be restored to some previous SQL Server version? No, definitely this is not the case as commercial tools like SQL Backup Recovery software come into play here and do this job with expertise. This can completely fix the error arising while making an attempt to restore SQL to lower version.

Conclusion

After going through this article, you are ready SQL Server database backup restore on lower version. The reason for the issue faced while restoring SQL backup on old version is that SQL Server by default does not allow restoring any database backup to some lower SQL Server’s version, in spite of its compatibility with the version from where backup was taken. But third-party tools help in this regard even if manual methods fail to do so.