2

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.

  • Which method did you use? What steps did you take (I doubt you followed the answer correctly)? – Mark Sinkinson May 7 '15 at 8:50
  • 1
    did you transfer the data using bcp? – Tom V May 7 '15 at 8:51
  • I have mentioned to run the query and put the results in a .bat file and then run the bat file. – Kin Shah May 7 '15 at 12:37
1
  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.

  • Thank you @Kin I have been following the step correctly now. Thank you for making it more understandable. – spideynok May 8 '15 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.