sql-server-2008sql-server-2012
I am trying to restore a SQL Server 2012 database after I set the compatibility level to SQL Server 2008 by following the instructions as listed on: http://learnsqlwithbru.com/2012/01/11/setting-database-compatibility-level-for-sql-server-2012/
However, the restore still doesn't work and I get "specified cast is invalid". Is there any way I can fix this problem?
Note: I don't want to use a Generate scripts option.
No, as I see it, you didn't miss anything. I have upgraded one of my databases from SQL Server 2000 to SQL Server 2012.
Another way is to upgrade the entire server – in this case you can use the upgrade wizard.
UPDATE: my upgrade process was successful, but be careful about discontinued and deprecated features in SQL Server 2008 and 2012.
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
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.
Best Answer
Compatibility level only controls what features may or may not be used on a given database and has no bearing on the restoration of DBs from different versions.
As pointed out by others, it can't be done by any means other than scripts or, if you can get it to work, the built in Copy Database tool. However that uses SSIS and I have never managed to get it to work successfully as it requires a whole heap of fiddly security apparatus, and usually consumes far more time than it's worth.
If you truly don't want to use scripting, then pretty much your only option is to upgrade your target DB server to 2012, or install a new one (using developer or Express editions is an option provided that a) It isn't a production instance if you use Developer Edition and b) the limitations of Express don't affect you).