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.
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).
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
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)
Run the bat file that will generate the .dat files in the folder that you have specified.
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)
Run the output using SSMS to insert data back in the tables.
This is very fast bcp method as it uses Native mode.
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.
There is no supported way to do this because SQL Server doesn’t allow this kind of compatibility.
What you can do is to
restore database on SQL 2012
generate scripts for objects and data
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.