{"id":177,"date":"2020-01-21T17:29:14","date_gmt":"2020-01-21T14:29:14","guid":{"rendered":"http:\/\/192.168.0.37\/wordpress\/?p=177"},"modified":"2020-04-28T15:49:08","modified_gmt":"2020-04-28T12:49:08","slug":"how-to-migrate-a-sql-server-database-to-a-lower-version","status":"publish","type":"post","link":"https:\/\/wiki.m-network.ru\/?p=177","title":{"rendered":"How to migrate a SQL Server database to a lower version"},"content":{"rendered":"\n<h5>Problem<\/h5>\n\n\n\n<p>Recently, we had a SQL Server database that was moved to SQL Server 2012 from  a lower version.&nbsp; After using it on SQL Server 2012, we had a request to get  the database running on a SQL Server 2008 R2 instance to satisfy a need. We tried  to do a backup and restore of the database, but we got the following error message:<\/p>\n\n\n\n<p class=\"has-text-align-center\">Msg 1813, Level 16, State 2, Line 1 <br>Could  not open new database &#8216;DatabaseName&#8217;. CREATE DATABASE is aborted. <br>Msg 948,  Level 20, State 1, Line 1 <br>The database &#8216;DatabaseName&#8217; cannot be opened because  it is version 655. This server supports version 611 and earlier. A downgrade  path is not supported. <\/p>\n\n\n\n<p>This\n<a href=\"https:\/\/www.mssqltips.com\/sqlservertip\/2675\/why-cant-i-restore-a-database-to-an-older-version-of-sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">\nerror message<\/a> is generated because SQL Server automatically upgrades the database \nwhen you restore or attach the database from a lower version to higher version and&nbsp;SQL \nServer does not allow you to restore or attach a database from a higher version \nof SQL Server to a lower version&nbsp;of SQL Server. <\/p>\n\n\n\n<p>In this tip, we will look at a one time procedure which we can follow to downgrade \nthe database from a higher version (SQL Server 2012) of SQL Server to a lower version \n(SQL Server 2008 R2) of SQL Server.<\/p>\n\n\n\n<h5>Solution<\/h5>\n\n\n\n<p>The error message in the problem statement occurs because the SQL Server database \nfiles (*.mdf, *.ndf and *.ldf) and backups are not backward compatible.&nbsp; Backward \ncompatibility is why we cannot restore or attach a database created from a higher \nversion of SQL Server to a lower version of SQL Server. However, there are a few \noptions that can help us to downgrade the database from a higher version of SQL \nServer to a lower version SQL Server. These options include:<\/p>\n\n\n\n<ul><li>Use the Generate Scripts Wizard in SQL Server Management Studio <\/li><li>Use SQL Server Integration Services <\/li><li>Create Custom Scripting and BCP<\/li><\/ul>\n\n\n\n<p>In this tip we will use the Generate Scripts Wizard in SQL Server Management \nStudio.&nbsp; <\/p>\n\n\n\n<p>Here are the basic steps we need to follow:<\/p>\n\n\n\n<ol><li>Script the database schema and data from the higher version of SQL Server \n\tby using the Generate Scripts Wizard in SSMS. <\/li><li>Connect to the lower version of SQL Server, and run the SQL scripts that \n\twere generated in the previous step, to create the database schema and data.<\/li><\/ol>\n\n\n\n<p>In the next section, I will demonstrate the steps for downgrading a SQL Server \n2012 database to SQL Server 2008 R2 database.\n\n\tNote: For demonstration purpose, I&#8217;ll be downgrading the OUTLANDER database \n\thosted on my SQL Server 2012 instance (IITCUK\\DEV01) to SQL Server 2008 R2 instance \n\t(IITCUK\\SQLSERVER2008).\n<\/p>\n\n\n\n<h2>Steps to Downgrade a SQL Server Database Using SSMS Generate Scripts Wizard<\/h2>\n\n\n\n<h4>Step 1<\/h4>\n\n\n\n<p>Script the schema of the <strong>OUTLANDER<\/strong> database on the SQL Server 2012 instance \n(IITCUK\\DEV01) using the <strong>Generate Scripts<\/strong> wizard in SSMS.<\/p>\n\n\n\n<p>In <strong>Object Explorer<\/strong> connect to <strong>IITCUK\\DEV01<\/strong>, right-click on the\n<strong>OUTLANDER<\/strong> database, expand <strong>Tasks<\/strong> and choose <strong>&#171;Generate Scripts&#8230;&#187;<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_01.jpg\" alt=\"In Object Explorer, right-click OUTLANDER database, expand Tasks and choose generate scripts\"\/><\/figure>\n\n\n\n<p>This launches <strong>Generate and Publish Scripts<\/strong> wizard. Click <strong>Next<\/strong>, \nto skip the <strong>Introduction<\/strong> screen and proceed to the <strong>Choose Objects<\/strong> \npage.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_02.jpg\" alt=\"SQL Server Management Generate and Publish Scripts Wizard - Introduction Screen\"\/><\/figure>\n\n\n\n<p>On the <strong>Choose Objects<\/strong> page, choose option <strong>&#171;Script entire database and \nall database objects&#187;<\/strong>, and then click <strong>Next<\/strong> to proceed to <strong>&#171;Set Scripting \nOptions&#187;<\/strong> page.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_03.jpg\" alt=\"Choose option Script entire database and all database objects in the SQL Server Management Studio Generate Scripts Wizard\"\/><\/figure>\n\n\n\n<p>On the <strong>Set Scripting Options<\/strong> page, specify the location where you want \nto save the script file, and then click the <strong>Advanced<\/strong> button.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_04.jpg\" alt=\"Choose option Specifying scripting options in the SQL Server Management Studio Generate Scripts Wizard\"\/><\/figure>\n\n\n\n<p>In the <strong>Advanced Scripting Options<\/strong> dialog box, <\/p>\n\n\n\n<ul><li>set <strong>Script for Server Version<\/strong> to <strong>SQL Server 2008 R2 <\/strong>(or whatever \n\tversion you want)<\/li><li>under the Table\/View Options, set <strong>Script<\/strong> <strong>Triggers<\/strong>,\n\t<strong>Script Indexes<\/strong> and <strong>Script Primary Keys<\/strong> to <strong>True<\/strong><\/li><li>and set <strong>Types of data to script<\/strong> to <strong>Schema and Data &#8212; <\/strong>this \n\tlast option is key because this is what generates the data per table.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_05.jpg\" alt=\"Choose option Set scripting options including scripting the Schema and Data\"\/><\/figure>\n\n\n\n<p>Once done, click <strong>OK<\/strong>, to close the <strong>Advanced Scripting Options<\/strong> dialog \nbox and return to <strong>Set Scripting Options<\/strong> page. In <strong>Set Scripting Options<\/strong> \npage, click <strong>Next<\/strong> to continue to <strong>Summary<\/strong> page.<\/p>\n\n\n\n<p>After reviewing your selections on <strong>Summary<\/strong> page, click <strong>Next<\/strong> to \ngenerate scripts.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_06.jpg\" alt=\"Generate and Publish Scripts - Summary Interface\"\/><\/figure>\n\n\n\n<p>Once scripts are generated successfully, choose the&nbsp;<strong>Finish<\/strong> button \nto close the <strong>Generate and Publish Scripts<\/strong> wizard.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_07.jpg\" alt=\"Generate and Publish Scripts wizard iterating through each object\"\/><\/figure>\n\n\n\n<h4>Step 2<\/h4>\n\n\n\n<p>Connect to the SQL Server 2008 R2 instance (IITCUK\\SQLSERVER2008), and then run \nthe SQL scripts that were generated in Step 1, to create the <strong>OUTLANDER<\/strong> database \nschema and data.<\/p>\n\n\n\n<p>In <strong>Object Explorer<\/strong> connect to <strong>IITCUK\\SQLServer2008<\/strong>, then in SQL \nServer Management Studio, open the SQL Server script you saved in Step 1.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_08.jpg\" alt=\"SSMS\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_09.jpg\" alt=\"Opening SQL Script to install OUTLANDER database\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_10.jpg\" alt=\"Opening SQL Script to install OUTLANDER database\"\/><\/figure>\n\n\n\n<p>Modify the script, to specify the correct location for the <strong>OUTLANDER<\/strong> database \ndata and log files. Once done, run the script to create the <strong>OUTLANDER<\/strong> database \non IITCUK\\SQLServer2008 instance.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_11.jpg\" alt=\"Restoring OUTLANDER database\"\/><\/figure>\n\n\n\n<p>Upon successful execution, refresh the <strong>Database<\/strong> folder in <strong>Object Explorer<\/strong>. \nAs you can see in the following image, the <strong>OUTLANDER<\/strong> database has been successfully \ndowngraded.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_12.jpg\" alt=\"Verifying OUTLANDER database downgrade\"\/><\/figure>\n\n\n\n<h2>Notes<\/h2>\n\n\n\n<p>There are a few things to be aware of when using this approach. <\/p>\n\n\n\n<ul><li>This solution creates one large SQL file that has the scripts to create \n\tthe database objects and also INSERT statements for the data in the tables.<\/li><li>For a large databases, the SQL file can get very large if you script out \n\tboth the schema and the data and could be hard to load into an editor.&nbsp; \n\tAlso, you may get a memory related error message from the editor if the file \n\tis too big.<\/li><li>For large databases, around 1GB or more, if this approach does not work, \n\tthen you should look at using SSIS to migrate the database or create custom \n\tscripts to script out the objects and BCP out the data for each of the tables.&nbsp; \n\tYou can use this Generate Scripts wizard to just generate the schema without \n\tthe data and use SSIS or BCP to export and import the data.<\/li><li>This approach works for SQL Server 2017 to SQL Server 2005.&nbsp; Some of \n\tthe scripting options might be a bit different in newer versions, but the process \n\tis still the same.<\/li><li>Before just executing the script, you should review the script to make sure \n\teverything looks correct such as the path of the database files, database options, \n\tetc.<\/li><li>Also if you are using new functionality that does not exist in the lower \n\tversion, SQL Server won&#8217;t be able to create the objects and you will need to \n\treview the scripts that were generated and update the code accordingly.<\/li><li>For a very simple database this approach should work pretty easliy, but \n\tyou might need to spend some time making some modifications to the script for \n\ta more complex database.<\/li><li>Below is a list of all of the scripting options. If you click on an item, \n\tthe bottom part of the screen gives you a short definition of the option.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/www.mssqltips.com\/tipimages2\/2810_script-options.png\" alt=\"script options\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Problem Recently, we had a SQL Server database that was moved to SQL Server 2012 from a lower version.&nbsp; After using it on SQL Server 2012, we had a request to get the database running on a SQL Server 2008 R2 instance to satisfy a need. We tried to do a backup and restore of ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/wiki.m-network.ru\/?p=177\" title=\"read more...\">Read more<\/a><\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[31],"tags":[11],"_links":{"self":[{"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=\/wp\/v2\/posts\/177"}],"collection":[{"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=177"}],"version-history":[{"count":2,"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=\/wp\/v2\/posts\/177\/revisions"}],"predecessor-version":[{"id":246,"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=\/wp\/v2\/posts\/177\/revisions\/246"}],"wp:attachment":[{"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wiki.m-network.ru\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}