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.

I have SQL Server 2008 R2 Express edition, and I want to export the data in that database to another computer so I can import it again in that computer.

How please?

What I have tried

Right-click in the database -> Tasks -> Export -> Set server name + password + set database source.

My problem

I found that I have to set the destination, but I didn't find the destination as a file or something like that, I just found that the destination is the same database which I am extracting the data from.

Help please, thanks in advance

share|improve this question
2  
Are you exporting from one SQL server, to another SQL server? If so, the best method is to do a backup from your source server, and restore onto the destination server. –  StanleyJohns Jul 8 '13 at 8:48
    
just the data? or the schema as well? –  Luis LL Jul 8 '13 at 14:24
    
@StanleyJohns where should I put that back up please? –  Marco Dinatsoli Jul 10 '13 at 11:56
    
@LuisLL I would transfer any thing in order to transfer the data from comp to comp. but absouluitly i already transftered the schema –  Marco Dinatsoli Jul 10 '13 at 11:57
add comment

migrated from stackoverflow.com Jul 8 '13 at 8:11

This question came from our site for professional and enthusiast programmers.

5 Answers

There are a few ways to do this, some easier and safer than others. You could mess around with detaching and attaching, but I will never recommend that solution for normal cases like these. Since you have your database on SQL Server Express Edition, then it can't be larger than 10 GB. It'll be a quick and painless process to just backup the database, and jump over to your other machine and do the restore.

The Import/Export features aren't worth the hassle in this case. Backup and restore would be the easiest route.

If you're looking to only move a certain amount of data (say, a handful of tables), then I'd just as soon generate the scripts (for data and schema if that's desired). You can do this by right-clicking the database in Object Explorer, Tasks, and Generate Scripts. You'll have numerous options for this task, and you can specify a subset of objects to script out. Then you could run that script on your destination instance.

Just to reiterate, I'd do the backup/restore for the entire database copy, or generate scripts for a subset of objects.

share|improve this answer
add comment

I found this article and I suggest you check it out:

http://support.microsoft.com/kb/314546

It explains how to move databases between computers that are running SQL Server. There are 6 steps described here:

Step 1: How to move user databases

Step 2: How to transfer logins and passwords

Step 3: How to resolve orphaned users

Step 4: How to move jobs, alerts, and operators

Step 5: How to move DTS packages

Step 6: Change the sp_configure settings to match the previous computer

share|improve this answer
add comment

Backup & Restore is safe and better option. Another solution is as below :

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
add comment

maybe you can using detach database, copy the database file .mdf , then use attach on new server.

share|improve this answer
add comment

@Marco Dinatsoli to expand on my comment Are you exporting from one SQL server, to another SQL server? If so, the best method is to do a backup from your source server, and restore onto the destination server.

  1. On your source SQL server, do a backup. Refer to this link to do the backup. Note the location of your backup file.
  2. Move the backup file to your destination server, to a location that is accessible to your destination SQL server.
  3. Restore you DB onto the destination server using the backup file. Maybe give it a new name so as not to overwrite anything. Refer to this link for the restore.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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