restore higher version database backup on lower version sql server – restore problem

Posted on : 29-12-2011 | By : Devi Prasad | In : SQL Errors

Share:

9


when you try to resore higher version database backup of sql server on a lower version of sql server you get an error:

The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

10.50.1600 is of higher verion then 10.00.1600.
Also you can get the same error with different versions where higher version is being restored on a lower version database server

This database restore compatability error occurs because you are trying to restore higher version on lower version.

Why this error: Database servers get changed with service packs and new releases. New object types get added and the lower versions cannot understand these object types.
Example: Common Table Expressions used SQL Server 2008 will be unknown for SQL Server 2000.
In order to avoid such conflicts and problems – Higher end database restorations cannot be performed directly on lower end database servers. It is also not possible to Attach the higher version database to lower version sql server.

Following link helps you identify the database backup version Identify SQL Server Database Backup Version

How to restore such databases?

The best way to do restoration is to upgrade the lower version SQL Server to the version greater than or equal to the higher version SQL Server.

There is no direct way to restore the database with out upgrading the server,In such a case following are the alternate ways to restore.

Alternate ways to restore higher database versions on lower version sql servers:

Script the database Objects and restore them on the server
To script the database objects you can use Generate Scripts Task.
Generate Scripts Task can be found in SQL Server management studio.
RightClick on the database -> Click On Tasks -> Then Click on Generate Scripts…
Run through the wizard.
In the set scripting options tab in the generate scripts wizard click on advanced and select the following options:
For ‘types of data to script’ option Select ‘Schema and data’
For ‘Script for version’ select the required target SQL Server version

Generate the scripts and run the generated scripts on the destination server.

You may have to run the script multile times when you face any error.
Create a new database every time you rerun the script.
Fix the errors that you may face due to order in which objects are created (or) data is inserted into tables different tables.

Use SQL Server Import Export Wizard to copy data from source server to destination server (only Data)
You can find Import Export Wizard in SQL Server management studio.

On the Destination Server (Lower database version server)
RightClick on the database -> Click On Tasks -> Then Click on Import.
Give the correct source and destination servers and select all tables.
This creates an SSIS package and select the option to run immedietly if you want to copy data immedietly.

Copy data to destination tables using BCP (only Data)
You can also use BCP to copy data from source server to destination server.
BCP out tables on source server and BCP in on the destination server.

Note: The above methods do not guarantee the similarity between the two databases.

SQL Server Versions:

11.00.1750 11.00.1440 11.00.1103 10.50.2789 10.50.2776 10.50.2772 10.50.2769 10.50.2500 10.50.1807 10.50.1804 10.50.1800 10.50.1797 10.50.1790 10.50.1777 10.50.1769 10.50.1765 10.50.1753 10.50.1746 10.50.1734 10.50.1720 10.50.1702 10.50.1617 10.50.1600 10.50.1352 10.50.1092 10.00.5768 10.00.5766 10.00.5500 10.00.5416 10.00.4323 10.00.4321 10.00.4316 10.00.4285 10.00.4279 10.00.4272 10.00.4266 10.00.4000 10.00.3798 10.00.2850 10.00.2847 10.00.2821 10.00.2816 10.00.2808 10.00.2804 10.00.2799 10.00.2789 10.00.2787 10.00.2775 10.00.2766 10.00.2757 10.00.2746 10.00.2740 10.00.2734 10.00.2723 10.00.2714 10.00.2712 10.00.2710 10.00.2573 10.00.2531 10.00.2520 10.00.1835 10.00.1828 10.00.1823 10.00.1818 10.00.1812 10.00.1806 10.00.1798 10.00.1787 10.00.1779 10.00.1771 10.00.1763 10.00.1750 10.00.1600 10.00.1442 10.00.1300 10.00.1075 10.00.1049 10.00.1019 9.00.5266 9.00.5259 9.00.5254 9.00.5057 9.00.5000 9.00.4912 9.00.4325 9.00.4317 9.00.4315 9.00.4311 9.00.4309 9.00.4305 9.00.4294 9.00.4285 9.00.4273 9.00.4268 9.00.4266 9.00.4262 9.00.4230 9.00.4226 9.00.4224 9.00.4220 9.00.4216 9.00.4211 9.00.4207 9.00.4053 9.00.4035 9.00.4028 9.00.3356 9.00.3355 9.00.3353 9.00.3330 9.00.3328 9.00.3325 9.00.3320 9.00.3318 9.00.3315 9.00.3310 9.00.3301 9.00.3294 9.00.3282 9.00.3260 9.00.3259 9.00.3259 9.00.3257 9.00.3246 9.00.3244 9.00.3240 9.00.3239 9.00.3232 9.00.3231 9.00.3231 9.00.3230 9.00.3228 9.00.3224 9.00.3221 9.00.3221 9.00.3221 9.00.3221 9.00.3215 9.00.3208 9.00.3206 9.00.3200 9.00.3194 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3182 9.00.3179 9.00.3178 9.00.3177 9.00.3177 9.00.3177 9.00.3175 9.00.3175 9.00.3175 9.00.3175 9.00.3175 9.00.3171 9.00.3169 9.00.3169 9.00.3166 9.00.3166 9.00.3161 9.00.3161 9.00.3161 9.00.3161 9.00.3159 9.00.3156 9.00.3155 9.00.3155 9.00.3155 9.00.3155 9.00.3154 9.00.3154 9.00.3154 9.00.3153 9.00.3152 9.00.3080 9.00.3077 9.00.3073 9.00.3068 9.00.3054 9.00.3050 9.00.3042 9.00.3033 9.00.3027 9.00.3026 9.00.2239 9.00.2237 9.00.2236 9.00.2236 9.00.2234 9.00.2233 9.00.2233 9.00.2233 9.00.2232 9.00.2231 9.00.2230 9.00.2229 9.00.2227 9.00.2226 9.00.2226 9.00.2223 9.00.2221 9.00.2219 9.00.2218 9.00.2216 9.00.2214 9.00.2214 9.00.2211 9.00.2211 9.00.2209 9.00.2208 9.00.2207 9.00.2207 9.00.2207 9.00.2206 9.00.2206 9.00.2206 9.00.2206 9.00.2202 9.00.2201 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2196 9.00.2196 9.00.2195 9.00.2194 9.00.2192 9.00.2192 9.00.2191 9.00.2190 9.00.2189 9.00.2187 9.00.2181 9.00.2181 9.00.2176 9.00.2176 9.00.2175 9.00.2175 9.00.2175 9.00.2175 9.00.2175 9.00.2174 9.00.2167 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2156 9.00.2153 9.00.2153 9.00.2050 9.00.2047 9.00.2040 9.00.2029 9.00.1561 9.00.1558 9.00.1554 9.00.1551 9.00.1551 9.00.1550 9.00.1550 9.00.1547 9.00.1545 9.00.1541 9.00.1541 9.00.1539 9.00.1538 9.00.1536 9.00.1534 9.00.1533 9.00.1532 9.00.1531 9.00.1528 9.00.1528 9.00.1528 9.00.1528 9.00.1528 9.00.1519 9.00.1518 9.00.1518 9.00.1518 9.00.1514 9.00.1503 9.00.1502 9.00.1500 9.00.1406 9.00.1399 8.00.2283 8.00.2282 8.00.2279 8.00.2273 8.00.2271 8.00.2265 8.00.2253 8.00.2249 8.00.2248 8.00.2246 8.00.2245 8.00.2244 8.00.2242 8.00.2238 8.00.2236 8.00.2234 8.00.2232 8.00.2231 8.00.2229 8.00.2226 8.00.2226 8.00.2223 8.00.2223 8.00.2218 8.00.2217 8.00.2215 8.00.2215 8.00.2215 8.00.2215 8.00.2209 8.00.2207 8.00.2201 8.00.2199 8.00.2197 8.00.2197 8.00.2197 8.00.2196 8.00.2194 8.00.2194 8.00.2192 8.00.2191 8.00.2191 8.00.2189 8.00.2189 8.00.2187 8.00.2187 8.00.2187 8.00.2187 8.00.2180 8.00.2180 8.00.2175 8.00.2172 8.00.2171 8.00.2168 8.00.2166 8.00.2162 8.00.2159 8.00.2156 8.00.2151 8.00.2151 8.00.2148 8.00.2148 8.00.2148 8.00.2148 8.00.2148 8.00.2148 8.00.2148 8.00.2147 8.00.2145 8.00.2145 8.00.2055 8.00.2040 8.00.2039 8.00.2026 8.00.1547 8.00.1037 8.00.1036 8.00.1035 8.00.1034 8.00.1029 8.00.1027 8.00.1025 8.00.1025 8.00.1024 8.00.1021 8.00.1020 8.00.1019 8.00.1017 8.00.1014 8.00.1014 8.00.1013 8.00.1009 8.00.1007 8.00.1003 8.00.1001 8.00.1000 8.00.997 8.00.996 8.00.996 8.00.994 8.00.994 8.00.994 8.00.993 8.00.993 8.00.993 8.00.991 8.00.990 8.00.988 8.00.985 8.00.980 8.00.977 8.00.973 8.00.972 8.00.970 8.00.967 8.00.962 8.00.961 8.00.959 8.00.957 8.00.955 8.00.954 8.00.952 8.00.952 8.00.952 8.00.949 8.00.948 8.00.944 8.00.937 8.00.936 8.00.935 8.00.934 8.00.933 8.00.929 8.00.928 8.00.927 8.00.926 8.00.923 8.00.922 8.00.919 8.00.916 8.00.915 8.00.913 8.00.911 8.00.910 8.00.908 8.00.904 8.00.892 8.00.891 8.00.879 8.00.878 8.00.876 8.00.876 8.00.876 8.00.873 8.00.871 8.00.871 8.00.870 8.00.869 8.00.866 8.00.865 8.00.865 8.00.863 8.00.863 8.00.859 8.00.858 8.00.857 8.00.857 8.00.857 8.00.856 8.00.854 8.00.852 8.00.852 8.00.851 8.00.850 8.00.850 8.00.850 8.00.848 8.00.847 8.00.845 8.00.845 8.00.844 8.00.842 8.00.841 8.00.840 8.00.840 8.00.839 8.00.839 8.00.837 8.00.837 8.00.837 8.00.837 8.00.837 8.00.819 8.00.818 8.00.818 8.00.818 8.00.818 8.00.818 8.00.816 8.00.814 8.00.811 8.00.811 8.00.811 8.00.807 8.00.804 8.00.801 8.00.800 8.00.800 8.00.800 8.00.798 8.00.794 8.00.794 8.00.794 8.00.794 8.00.791 8.00.790 8.00.789 8.00.788 8.00.781 8.00.780 8.00.780 8.00.780 8.00.779 8.00.776 8.00.775 8.00.769 8.00.769 8.00.765 8.00.765 8.00.765 8.00.765 8.00.763 8.00.762 8.00.760 8.00.743 8.00.743 8.00.741 8.00.736 8.00.735 8.00.733 8.00.730 8.00.728 8.00.725 8.00.725 8.00.723 8.00.721 8.00.721 8.00.718 8.00.715 8.00.715 8.00.714 8.00.713 8.00.710 8.00.705 8.00.703 8.00.702 8.00.701 8.00.701 8.00.700 8.00.696 8.00.696 8.00.695 8.00.695 8.00.695 8.00.693 8.00.690 8.00.689 8.00.688 8.00.686 8.00.682 8.00.679 8.00.678 8.00.667 8.00.665 8.00.661 8.00.655 8.00.652 8.00.650 8.00.644 8.00.608 8.00.604 8.00.599 8.00.594 8.00.584 8.00.578 8.00.578 8.00.561 8.00.558 8.00.558 8.00.552 8.00.552 8.00.534 8.00.532 8.00.475 8.00.474 8.00.473 8.00.471 8.00.469 8.00.452 8.00.444 8.00.444 8.00.443 8.00.428 8.00.384 8.00.296 8.00.287 8.00.251 8.00.250 8.00.249 8.00.239 8.00.233 8.00.231 8.00.226 8.00.225 8.00.223 8.00.222 8.00.218 8.00.217 8.00.211 8.00.210 8.00.205 8.00.204 8.00.194 8.00.190 8.00.100 8.00.078 8.00.047 7.00.1149 7.00.1143 7.00.1143 7.00.1097 7.00.1094 7.00.1094 7.00.1092 7.00.1087 7.00.1079 7.00.1078 7.00.1077 7.00.1063 7.00.1033 7.00.1026 7.00.1004 7.00.996 7.00.978 7.00.977 7.00.970 7.00.970 7.00.961 7.00.921 7.00.919 7.00.918 7.00.917 7.00.910 7.00.905 7.00.889 7.00.879 7.00.857 7.00.843 7.00.842 7.00.839 7.00.835 7.00.776 7.00.770 7.00.745 7.00.722 7.00.699 7.00.689 7.00.677 7.00.662 7.00.658 7.00.657 7.00.643 7.00.623 7.00.583 7.00.517

(Visited 1,510 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (9)

[…] Backup was taken on a higher version of SQL Server and restoration is being done on the earlier version. The following post gives you more details about this: restore higher version database backup on lower version sql server – restore problem […]

[…] posts: Verify SQL Server Database Backup restore higher version database backup on lower version sql server – restore problem The media family on device is incorrectly formed. SQL Server cannot process this media family […]

Dear Sir,

How can I retore the back up from SQL Server 2000 to a SQL Server 2012 Database. The Database backup is a .dat file and its size is around 8 GB and need to be restored in the New SQL Server 2012 trial version.

I tried many ways but not been successful.

As you are an Expert I need your help. Basically I am an Applications Developer. Do not know much about DBA.

Thanks & regards,

Jaleel Ahmed Qureshi

What is the exact error you are getting when trying to restore the database?
Also Verify your database backup – http://sqlserverlearner.com/2012/verify-sql-server-database-backup

Check this link for details on restoring database – http://msdn.microsoft.com/en-us/library/ms177429.aspx

hi gest generate screpts from old version with data and run the screpts into 2012 in order like firest schemas,tables,function,view,sps like this order we can run the screpts it will be successfull

Dear,

I am unable to restore data base from SQL server 7.00.063 to sql server 2008 (10.00.1600). I tried many times but i didnt get.
Is there any chance to restore or not.
please be need full.
Thanks in advance.

Dinesh-
SQL Server 7.0 databases cannot be directly restored on SQL Server 2008.
To restore on SQL Server 2008 the compatability of database should be atleast 80 i.e SQL Server 2000.

So, first restore the database on SQL Server 2005 OR SQL Server 2000.
Later take the backup from SQL Server 2005 OR SQL Server 2000 and resore on SQL Server 2008.

Thank you!

I did the backup in 2012 and was trying to restore in 2008 R2 and it wasn’t working.

I was able to restore the backup in the same 2012, so it can only be a version problem since the file wasn’t corrupted.

So just to be clear, there is no way to restore a .bak file created using SQL 2012 to a SQL 2005 installation, correct?

Write a comment



Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,