Restore SQL Server 2012 backup to a SQL Server 2008 database?

Question

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.

Answer 1

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.

Answer 2

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

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.

Answer 3

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.

Answer 4

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.

Answer 5

Checkout these references would be help you:

http://msdn.microsoft.com/en-us/library/ms140052.aspx

How can I backup & restore a single FILEGROUP in Sql Server 2008

Thanks