42

Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008?

I tried to attach file, it does not work.

Improve this question
3

3 Answers 3

Reset to default
31

You have couple of options :

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

  1. 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)

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

  3. 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)

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

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

Improve this answer
4
  • Very helpful - method B worked for me (SQL generated by method A was 7GB and SSMS was having none of it). Some SSIDs and linked servers didn't go across properly, but when does anything to do with linked servers ever work first time? Not sure I would be happy using this in production but for getting a test environment 95% up quickly it was perfect.
    – aucuparia
    Commented Dec 4, 2014 at 8:45
  • @aucuparia Some SSIDs and linked servers didn't go across properly. Linked servers has to be scripted out manually. SSIDs can be trnasfered using sp_helprevlogin. SQLAgent jobs, ssis packages etc should be moved as per your needs. The point of this method is to get the data as fast as possible when you are downgrading or merging 2 databases.
    – Kin Shah
    Commented Dec 4, 2014 at 15:12
  • Option B was the only method that worked for me, as the scripts generated were too large in the other ways
    – JumpingJezza
    Commented Sep 26, 2018 at 8:36
  • I wish I could upvote this more than once! Wasted hours trying to get the data in before I discovered this answer. Thanks very much
    – Avrohom Yisroel
    Commented Jan 6, 2020 at 21:30
23

No, you cannot go backward, only forward. You can create an empty database on 2008 and then use the Generate Scripts wizard in Management Studio to script the schema and data (or 3rd party comparison tools from Red Gate and others). Make sure you set the right target version as 2008, and you'll have to flesh out incompatible things (e.g. OFFSET or FORMAT) that you might have used in 2012.

Improve this answer
8

There is no supported way to do this because SQL Server doesn’t allow this kind of compatibility.

What you can do is to

  1. restore database on SQL 2012

  2. generate scripts for objects and data

  3. clean up scripts from all details that are unique for SQL 2012
  4. execute scripts on 2008

If you don’t have SQL Server 2012 then you can use third party tools to read backup and extract data and structure.

In this case just create empty database on SQL 2008 and use tools such as ApexSQL Diff and ApexSQL Data Diff to synchronize objects and data. You can find these from other major vendors too such as Red-Gate or Idera.

Improve this answer