Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

enter image description here

As I show , I have server database version-10.50.1600 and local database version-10.0.1600 .
How can I copy database (with all tables and data(s)) from server to local which has different version ? I use MS sql server 2008 R2 .

share|improve this question

marked as duplicate by Jon Seigel, StanleyJohns, Max Vernon, dezso, Mat May 28 '13 at 15:30

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
I presume there's also the option of upgrading local to the same version as server, then doing backup/restore or the other usual suspects. –  Mat May 28 '13 at 15:31
add comment

3 Answers 3

As I show , I have server database version-10.50.1600 and local database version-10.0.1600 . How can I copy database (with all tables and data(s)) from server to local which has different version ?

Since you are trying to load data from higher version to lower version, this is not supported.

As others have pointed out to script out database objects + data, depending on the database data size, I would take a decision.

If the database size is few MB then, that is a good approach.

If the database size is huge or in GB, then the generated script wont be either handled by SSMS nor SQLCMD.

Below is my suggestion :

  1. Script out the database SCHEMA_ONLY and recreate an empty database on the destination server.
  2. Use BCP OUT and BULK INSERT to insert data.

Below is the script that will help you with Part 2.

/************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another*********************************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- 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_OUT\*.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_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)                         



--- 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) 
share|improve this answer
    
Thanks for the awesome script –  bogert Aug 29 '13 at 5:57
add comment

Honestly, the easiest way to do this will be to upgrade your local instance to 2008 R2, or install a second local instance of 2008 R2. Then it's just a matter of making a database backup from the server, copying it to your local machine, and restoring it on a local instance.

If you really need to transfer the database to 2008, for development or troubleshooting on that version, you'll have to do all sorts of unpleasant things like scripting out all database objects (including indexes, security, etc), transferring the data (bcp or SSIS), and hoping that your database isn't using any features that are new to 2008 R2.

It's much less hassle to just upgrade.

share|improve this answer
add comment

Your best bet here would be to script out the database (schema and data) and run that script on the lower version server (provided that the database is of and acceptable size to script out). The reason you can't just do a traditional backup and restore is because you're taking a database from SQL Server 2008 R2 to SQL Server 2008 (higher version to lower version). You can't restore a database to a lower instance version.

To script out the database, right click on it in Object Explorer, mouse over Tasks and select Generate Scripts.... There will be a handful of configuration options, and you'll have to select to generate the script for schema and data.

share|improve this answer
add comment

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