Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What was the DB compatibility version of the SQL DB backed-up? –  SliverNinja Jul 9 '12 at 16:48
4  
@SilverNinja compatibility level does not matter. –  Aaron Bertrand Jul 9 '12 at 16:56
    
Nope. backup / restore is not backwards compatible. I wouldn't trust it. Import/export if you want to sleep at night. –  Tony Hopkinson Jul 9 '12 at 16:58
    
I've seen people try broggling a backup backwards, results never been worth the effort or risk. –  Tony Hopkinson Jul 9 '12 at 17:00
    
@TonyHopkinson What does "broggling" mean? Results are simply not possible unless you use a hex editor to modify the MDF/LDF files to make SQL Server think they're older than they are. –  Aaron Bertrand Jul 9 '12 at 17:20
show 1 more comment

migrated from stackoverflow.com Jul 9 '12 at 17:00

This question came from our site for professional and enthusiast programmers.

5 Answers

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

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

It won't work. The only way I think, but not sure, is to use the Import and Export utility. As far as data types of both are same, there shouldn't be any issues.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.