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

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

7



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




Reference : Devi Prasad (sqlserverlearner.com)

Do you like my blog?

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



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.




Your Ad Here


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