[sql] How to restore a SQL Server 2012 database to SQL Server 2008 R2?

I am trying to restore the backup taken from a SQL Server 2012 to SQL Server 2008 R2, and it giving an error

Specified cast is not valid. (SqlManagerUI)

If you have any solution to this please give comment

thanks.

The answer is


Right click on your database and select Tasks -> Generate Scripts

Select things you want to restore or just let the complete database be selected by default.

Now click Next -> Click Advanced and Choose 'Script for Server Version' to the 'ssms version' you want to restore like 2008 or 2008 R2 or other.

also, select 'type of data to script' to 'Schema and Data'

Finally execute this .sql file on destination ssms and Change Database Name in first Line Use Database

if database is not there then create one and if you get error with executing the script execute the script from alter keyword to complete bottom.


You won't be able to restore from 2012 to 2008. You will be able to use a tool like red-gate SQL compare to copy the schema etc (provided nothing 2012 specific is used). If you have data to copy across too, you can use their Data Compare tool, and I think you get a 14 day free trial.


The only built-in way to "downgrade" a database from one SQL Server version to a lower one is the hard way: Script out the whole database, schema and data, then execute the script on the target server.

This is do-able but tends to be brutal.


To: Killercam Thanks for your solutions. I tried the first solution for an hour, but didn't work for me.

I used scripts generate method to move data from SQL Server 2012 to SQL Server 2008 R2 as steps bellow:

In the 2012 SQL Management Studio

  1. Tasks -> Generate Scripts (in first wizard screen, click Next - may not show)
  2. Choose Script entire database and all database objects -> Next
  3. Click [Advanced] button 3.1 Change [Types of data to script] from "Schema only" to "Schema and data" 3.2 Change [Script for Server Version] "2012" to "2008"
  4. Finish next wizard steps for creating script file
  5. Use sqlcmd to import the exported script file to your SQL Server 2008 R2 5.1 Open windows command line 5.2 Type [sqlcmd -S -i Path to your file] (Ex: [sqlcmd -S localhost -i C:\mydatabase.sql])

It works for me.


NOTE, SOME OF THE ABOVE ANSWERS ARE MASSIVELY OUT-OF-DATE! THIS CAN BE DONE AND ALL WITHIN SQL SERVER MANAGEMENT STUDIO (SQL MS)


There are numerous methods you can adopt to "downgrade" a database, but one I have found recently and that I believe was not found in early releases of SQL MS 2012, is the Copy Database Wizard. Here is how you can copy a database from a 2012 server instance to a 2008 R2 instance:

  1. In the 2012 instance, right click on the database you want to copy/"downgrade" and select "Tasks" > "Copy Database...".

  2. "Welcome to the Copy Database Wizard" click [Next].

  3. "Select a Source Server": Set the "Source server" as the 2012 instance (or the higher version server instance), and set the appropriate authentication. click [Next]. [Note. the Server Agent services must be running]

  4. "Select a Destination Server:" Set the "Destination server" as the 2008 R2 (or lower version instance), and set the appropriate authentication. click [Next]. [Note. the Server Agent services must be running]

  5. "Select the Transfer Method" For the sake of this example, select "Use the SQL Management Object method", click [Next].

  6. Select to move or copy the required databases, click [Next].

  7. Configure the destination database path and logical names etc. Select the required option for if the database exists. Click [Next].

  8. Configure the integration services package, click [Next].

  9. For this example, select the "Run Immediately" option for "Schedule the Package" options, click [Next].

  10. "Complete the Wizard", click [Finish] to execute the package and create the "downgraded" database.

You are done, happy days. :]


Another method I found was the SQL Database Migration Wizard which was created by Microsoft and which I think (I don't know) that the wizard above was created from. Get it here http://sqlazuremw.codeplex.com/. To use this package to migrate a databases from SQL Server 20012 to 2008 R2, you can do the following:

Note. Microsoft have now removed SQLAzureMW from Codeplex. I have personally made it available here

  1. Run SQLAzureMW.exe.

  2. Select the Analyse/Migrate radio button from the right hand side of the main window.

  3. Select the Target Server as “SQL Database latest service version (V12)”. Click [Next].

  4. Connect to the SQL Server 2012 instance. Server name for my machine is “VAIOE\SQLSERVER2012”, use Windows authentication, select “Master DB (list all databases)” from the database options and “Save Login Information”. Click [Connect].

  5. Select the required database to migrate [use GVH Report database for now]. Click [Next].

  6. Select “Script all database objects”.

  7. Click [Advance] and change the following options:

a. Under General set “Target Server” to “SQL Server”.

b. Under “Table/View Options” set “Script Table / Data” to “Table Schema with Data”. Set “Database Engine Stored Procedures” to “True”. Set “Security Functions”, “Security Stored Procedures” and “System Functions” to “True”.

Click [OK]. Click [Next].

  1. Review your selections. Click [Next].

  2. You will be prompted “Ready to Generate Script?”, click [Yes]. This will start the script generation. Once this is done, click [Next].

  3. Now you will get another connection dialog. This time select the database on the target server (the SQL Server 2008 R2 instance). Select Master database so you get a choice of target DB. Click [Connect].

  4. Now, it is likely that you want to migrate into a new database, so click [Create Database].

  5. Enter a database target name and leave the “Collation” as the “”, this does not concern us. Click [Create Database]. Click [Next].

  6. You will now be prompted “Execute script against destination server?”, click [Yes].

  7. This will now go off and do loads of stuff, setting up the schema using the generated script, but unlike the previous method we found, the data is bulk loaded using BCP, which is blazingly fast. All of this is also done internally, so no generation of massive .sql script files etc.

  8. Click [Exit].

You are done. Now if you open up Management Studio and connect to both the SQL Server 2012 and 2008 R2 instances we have just worked with you can see that the schema for the 2012 source database matches the target database which was just created.


The two processes above are almost identical and provide the same functionality. I would not perform the latter unless you specifically need to migrate to Azure or method 1 fails for you.

I hope this helps someone out.


Here is another option which did the trick for me: https://dba.stackexchange.com/a/44340

There I used Option B. This is not my idea so all credit goes to the original author. I am just putting it in here also as I know that sometimes links don't function and it is recommended to have the full story handy.

Just one tip from me: First resolve the schema incompatibilities if any. Then pouring in the data should be a breeze.


Option A: Script out database in compatibility mode using Generate script option:

Note: If you script out database with schema and data, depending on your data size, the script will be massive and wont be handled by SSMS, sqlcmd or osql (might be in GB as well).

enter image description here

Option B:

First script out tables first with all Indexes, FK's, etc and create blank tables in the destination database - option with SCHEMA ONLY (No data).

Use BCP to insert data

I. BCP out the data using below script. Set SSMS in Text Mode and copy the output generated by below script in a bat file.

-- save below output in a bat file by executing below in SSMS in TEXT mode

-- clean up: create a bat file with this command --> del D:\BCP\*.dat 

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" ' /* path to BCP.exe */
    +  QUOTENAME(DB_NAME())+ '.' /* Current Database */
    +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
    +  QUOTENAME(name)  
    +  ' out D:\BCP\'  /* Path where BCP out files will be stored */
    +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
    +  REPLACE(name,' ','') 
    + '.dat -T -E -SServerName\Instance -n' /* ServerName, -E will take care of Identity, -n is for Native Format */
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       /* sysdiagrams is classified my MS as UserTable and we dont want it */
/*and schema_name(schema_id) <> 'unwantedschema'    */                             /* Optional to exclude any schema  */
order by schema_name(schema_id)

II. Run the bat file that will generate the .dat files in the folder that you have specified.

III. Run below script on the destination server with SSMS in text mode again.

--- Execute this on the destination server.database from SSMS.

--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destinationDB' /* Destination Database Name where you want to Bulk Insert in */
select 'BULK INSERT '
/*Remember Tables must be present on destination database */ 
+ QUOTENAME(@Destdbname) + '.' 
+ QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) 
+ '.' + QUOTENAME(name) 
+ ' from ''D:\BCP\' /* Change here for bcp out path */ 
+ REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') 
+ '.dat'' with ( KEEPIDENTITY, DATAFILETYPE = ''native'', TABLOCK )' 
+ char(10) 
+ 'print ''Bulk insert for ' + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') + ' is done... ''' 
+ char(10) + 'go'
from sys.tables
where is_ms_shipped = 0
and name <> 'sysdiagrams' /* sysdiagrams is classified my MS as UserTable and we dont want it */
--and schema_name(schema_id) <> 'unwantedschema' /* Optional to exclude any schema */
    order by schema_name(schema_id)

IV. Run the output using SSMS to insert data back in the tables.

This is very fast BCP method as it uses Native mode.


As has been mentioned already, you cannot use the "Back up" and "Restore" features to go from a SQL Server 2012 DB to a SQL Server 2008 DB. A program I wrote, SQL Server Scripter, will however connect to a SQL Server database and script out a database, its schema and data. It can be git cloned from BitBucket, and compiled with Visual Studio 2010 or later (if it's a later version, just open the .csproj).


Merge replication. You can create the subscriber (2008) from the distributor (2008). After the database has fully synchronized, drop the subscription and the publication.


If you are in same network then add the destination server to the MS Server management studio using connect option and then try exporting from source to destination. The most easiest way :)


You can't, you can NEVER restore from a higher version to a lower version of SQL Server. Your only option is to script out the database and then transfer the data via SSIS, BCP, linked server or scripting out the data


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to sql-server-2008-r2

A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233) Rebuild all indexes in a Database How to get last 7 days data from current datetime to last 7 days in sql server Get last 30 day records from today date in SQL Server what is numeric(18, 0) in sql server 2008 r2 Conversion failed when converting date and/or time from character string in SQL SERVER 2008 'MOD' is not a recognized built-in function name How to Convert datetime value to yyyymmddhhmmss in SQL server? SQL Server stored procedure Nullable parameter SQL Server 2008 R2 can't connect to local database in Management Studio

Examples related to sql-server-2012

Count the Number of Tables in a SQL Server Database SQL Server IF EXISTS THEN 1 ELSE 2 Get last 30 day records from today date in SQL Server No process is on the other end of the pipe (SQL Server 2012) How to subtract 30 days from the current date using SQL Server Possible to restore a backup of SQL Server 2014 on SQL Server 2012? SQL Server: Best way to concatenate multiple columns? SQL Server - An expression of non-boolean type specified in a context where a condition is expected, near 'RETURN' SSIS Excel Connection Manager failed to Connect to the Source Sql server - log is full due to ACTIVE_TRANSACTION