Copy database to local from server [duplicate]

Question

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 .

Answer 1

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) 

Answer 2

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.

Answer 3

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.