Sql-server – Moving SQL Server 2012 Database to SQL Server 2008 R2

sql serversql-server-2008-r2

I follow the answer of Mr. Kin in this post

How to move a database from SQL Server 2012 to SQL Server 2005

I run the query without errors, but when I check my blank database created on SQL Server 2008 R2, there's no change or new added data on my database.

Please help.

Sorry for duplicate post, I don't have enough reputation to comment on the link that I put here. Sorry.

Best Answer

  1. Script out schema using SSMS GUI. And using this script create the database on the destination server.

  2. Run below script on the source Server in SSMS with Text output CTRL+T:

    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_OUT\'                                           -- 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) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
order by schema_name(schema_id)

    In above script change the bcp.exe path to match. I have commented out the schema part in above script as you want to migrate all data from all schemas.

    Once you have the script output in TEXT format, open a notepad and paste it in and save it as .BAT file.

    Once saved, run the .BAT file by launching the CMD prompt. This will produce all the .dat files in the folder D:\BCP_OUT\ <-- change this as per your location.

  3. Now you will have all the .dat files. These are the BCP OUT files with data. So run the below script on the destination server. You can copy all the bcp files to the destination server or just use SSMS from the source server to generate the scripts.Make sure you change the bcp.exe path and the destination_database_Name. 

    --- 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 = 'destination_database_Name'               -- 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_OUT\'                             -- 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) <> 'some_schema_exclude'         -- Optional to exclude any schema 
order by schema_name(schema_id)

    Above will generate scripts for bulk insert using SSMS set in TEXT MODE as output. Copy the generated script and run it in another window using SSMS.

Hope that helps.

Related Question