SQL Server database backup restore on lower version

Question

How to restore a higher version SQL Server database backup file onto a lower version SQL Server   Using SQL Server 2008 R2  10 50 1600   I made a backup file and now I want to restore it on my live server s SQL Server 2008  10 00 1600    When I tried to restore the backup onto SQL Server 2008 it gives an error i e  Restore Failed because      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    How do I restore the backup file on this server

User · Accepted Answer

No  is not possible to downgrade a database  10 50 1600 is the SQL Server 2008 R2 version  There is absolutely no way you can restore or attach this database to the SQL Server 2008 instance you are trying to restore on  10 00 1600 is SQL Server 2008   Your only options are     upgrade this instance to SQL Server 2008 R2 or restore the backup you have on a SQL Server 2008 R2 instance  export all the data and import it on a SQL Server 2008 database

User · Answer

You can generate script from Task menu  For detailed reference  How to migrate a SQL Server database to a lower version

User · Answer

You can use functionality called Export Data-Tier Application which generates  bacpac file consisting database schema and data   On destination server  you can use Import Data-Tier Application option which creates and populates new database from pre-created  bacpac file  If you want just to transfer database schema  you can use Extract Data-Tier Application for creating file and Deploy Data-Tier Application for deploying created database schema   I ve tried this process on different versions of SQL Server from SQL 2014 to SQL 2012 and from SQL 2014 to SQL 2008R2 and worked well

User · Answer

I appreciate this is an old post  but it may be useful for people to know that the Azure Migration Wizard  available on Codeplex - can t link to is as Codeplex is at the moment I m typing this  will do this easily

User · Answer

Go to Task- Generate Scripts      In Advanced in  Types of data for script  select  Schema and data  and try to run this script in your lower version

User · Answer

You can not restore database  or attach  created in the upper version into lower version  The only way is to create a script for all objects and use the script to generate database        select  Schema and Data  -   if you want to Take both the   things in to the Backup script file    select Schema Only -   if only schema is needed      Yes  now you have done with the Create Script with Schema and Data of the Database

User · Answer

It s not pretty  but this is how I did it granted you have this option installed on your SQL 2008 R2 install    1  Right click database in SQL Server 2008 R2  Tasks     Generate scripts  in the wizard  select the entire database and objects in first step  On the  Set Scripting Options  step you should see a button  Advanced    select this and make sure you select  Script for Server Version    SQL Server 2008  not R2 version   This is a crucial step  because  import data  by itself does not bring along all the primary keys  constriants and any other objects like stored procedures     2  Run the SQL script generated on the new install or database instance SQL Express or SQL Server 2008 using the query window or open saved  sql script and execute and you should see the new database   3  Now right click on the new database and select  Tasks     Import Data    choose source as the R2 database and the destination as the new database   Copy data from one or more tables or views   select the top checkbox to select all tables and then next step  run the package and you should have everything on a older version  This should work for going back to a 2005 version as well  Hope this helps someone out

User · Answer

Will not necessarily work   Backup   Restore - will not work when the target is an earlier MS SQL version  Copy Database - will not work when the target is SQL Server Express   The destination server cannot be a SQL Server 2005 or later Express instance   Data import - Will not copy the schema    Will work   Script generation - Tasks -  Generate Scripts  Make sure you set the desired target SQL Server version on the Set Scripting Options -  Advanced page  You can also choose there whether to copy schema  data  or both  Note that in the generated script  you may need to change the DATA folder for the mdf ldf files if moving from non-express to express or vice versa  Microsoft SQL Server Database Publishing Services - comes with SQL Server 2005 and above  I think  Download the latest version from here  Prerequisites  sqlncli msi sqlncli x64 msi sqlncli ia64 msi  SQLServer2005 XMO msi SQLServer2005 XMO x64 msi SQLServer2005 XMO ia64 msi  download here

User · Answer

you can use BCP in and out for small tables   BCP OUT command -  BCP  SELECT    FROM  Dinesh   dbo   Invoices   QUERYOUT C  av Invoices1 txt -S MC0XENTC -T -c -r c  error csv   BCP IN command - Create table structure for Invoicescopy1    BCP  Dinesh   dbo   Invoicescopy1  IN C  av Invoices txt -S MC0XENTC -T -c

User · Answer

Another way to do this is to use  Copy Database  feature   Find by right clicking the source database    Tasks     Copy Database     You can copy the database to a lower version of SQL Server Instance  This worked for me from a SQL Server 2008 R2  SP1  - 10 50 2789 0  to Microsoft SQL Server 2008  SP2  - 10 0 3798 0

User · Answer

You can try this    Create a Database onto SQL Server 2008  Using Import Data feature import data from SQL Server R2  or any higher version   use  RedGate SQLCompare  to synchronize script

User · Answer

Here are my 2 cents on different options for completing this    Third party tools  Probably the easiest way to get the job done is to create an empty database on lower version and then use third party tools to read the backup and synchronize new newly created database with the backup   Red gate is one of the most popular but there are many others like ApexSQL Diff    ApexSQL Data Diff  Adept SQL  Idera      All of these are premium tools but you can get the job done in trial mode     Generating scripts  as others already mentioned you can always script structure and data using SSMS but you need to take into consideration the order of execution  By default object scripts are not ordered correctly and you   ll have to take care of the dependencies  This may be an issue if database is big and has a lot of objects   Import and export wizard  This is not an ideal solution as it will not restore all objects but only data tables but you can consider it for quick and dirty fixes when it   s needed

User · Answer

You d have to use the Import Export wizards in SSMS to migrate everything  There is no  downgrade  possible using backup restore or detach attach  Therefore what you have to do is    Backup the database from the server running the new SSMS SQL version  Import data from the generated  bak file  by expanding the  Tasks  menu after right-clicking the target database  and selecting the  Import Data  option

[sql-server] SQL Server database backup restore on lower version

Examples related to sql-server