If you would like to migrate the data in a SQL Server Database from development server to production server or vice versa , there is a tool called DTSWizard which comes with SQL Server installation that import and export data between a Data Source and Destination Source.
These instructions below will show step by step on How to export data from a database to another database with SQL Server 2008.
Note: steps below are for the export data process, for import data process, the steps are the same, you just reverse the source and destination.
1. Open the SQL Server Import and Export Wizard.
Right-Click on a Database => Task => Export Data
Open DTSWizard From Database
— Or —
From Start Menu => Microsoft SQL Server 2008 R2 => Import and Export data (64-bit)
Open DTSWizard From Start Menu
— Or —
Open the wizard by finding the DTSWizard.exe executable file located on your SQL Server installation folder. It should be available at:
- C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn
- C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn
2. Click Next on the Welcome screen.
3. Assign database information for source includes: Server name, Username and Password if use SQL Server Authentication mode or Use Windows Authentication mode.
After that, Click Next
Assign database information for source
4. Assign database information for destination includes: Server name, Username and Password if use SQL Server Authentication mode or Use Windows Authentication mode.
After that, click Next
Assign database information for destination
5. Select the first option: Copy data from one or more tables or views
Select Export Options
Click Next
6. The next step will ensure we migrate the appropriate table data. Select on which tables you want to export.
Edit Mappings
Please make sure all desired tables are included and mapped accordingly.
Click Edit Mappings … for all tables:
Column Mapping
Select “Delete rows in destination table”
And select “Enable identity insert”
Click OK
7. Repeat those steps for all tables in the transfer. Once complete click the Next button.
8. You need to select “Execute immediately”
Run Export Import
Click the Next button.
The final stage of this transfer/export process will perform the task.
The tutorial is on MS SQL Server 2008, but it should be applied if you’re on MS SQL Server 2005 or even MS SQL Server 2000
