Breadcrumbs

Home Technical Help Data transfer between Microsoft SQL Servers | Export Import database
Data transfer between Microsoft SQL Servers | Export Import database PDF Print E-mail
Written by Administrator   

If you are trying to transfer data between MS SQL servers, specially between 2 visions such as downgrading from a SQL server 2012 to SQL server 2008 you may run into SQL management studio errors .

To resolve the errors I have found the best method is to export all the data tables individually into a flat file such as CSV or TXT file or an Excel file format.

 

Export in a SQL database:

1. Open Microsoft SQL Management Studio > Connect to the database server

2. Right click on the database you want to export

3. Navigate to Task >  click on Export Data

4. In the SQL Server Import and Export Wizard > Click Next

5. In the Choose a Data Source window you database should already be selected if not find the database from the dropdown.

6. In the Choose a Destination window > click the Destination dropdown > select Flat File Destination. This will create a text file or a csv file with comma separated delimiters. Or select Microsoft Excel.

7. In the File name > click Browse select the destination to save the file and enter a file name. Extension .txt will create a text file .csv a CSV file.

8. Tick Column names in the first data row > click Next > in the Specify Table Copy or Query > Select Copy data from one or more tables or views > click Next

9. In the  Configure Flat File Destination > select SQL table from the Source table or view dropdown  > click Next .

10. Click Next in the Run Package window > click Finish in Complete the Wizard.

11. The table data will be exported to a txt file which will contain only one table. Or in a Excel file all tables will be exported if the database is not too large (else you may get error in exporting).

 

Import in a flat file into a SQL database:

12. Follow the above steps 1 to 2 then > click on Import Data

13. Click Next in the SQL Server Import and Export Wizard.

14. In the Choose a Data Source window > click the Data source: dropdown > select Flat File Source.

15. In the File name > click Browse select the file from your computer.

16. Tick Column names in the first data row.

17. On the left navigation > click Advanced. You should be able to see all the column names. For each of the columns you may have to match the source data type to destination data type. Most common is the DataType > string [DT_STR],  OutputColumnWidth > 255 > click Next.

18. In the Choose a Destination window ensure the correct database is selected > click Next.

19. In Select Source Tables and Views > click the Destination drop down and select the corresponding data table.

20. Click Edit Mappings... button.  In the Column Mappings window ensure the Append rows to the destination table is selected  >  tick Enable identity insert > click OK.

21. In the Review Data Type Mapping window check an error. If needed fix them >  click Next (leave most setting in default).

22. click Next in the Run Package window > click Finish in Complete the Wizard.