SQL Server Management Studio (SSMS) provides the Export Wizard task which you can use to copy data from one data source to another. You can choose from a variety of source and destination data source types, select tables to copy or specify your own query to extract data, and save your work as an SSIS package.

EXPORT DATA

• Create RDP instance

• Install Microsoft SQL Management Studio

• Install Microsoft SQL Server

• Locate your database in the SSMS Object Explorer

• Right click on your database in the Object Explorer, select Tasks, then Export Data from the context menu to launch the Export Wizard.  Click Next to advance past the Welcome dialog (if shown). In the following sections, we will walk through the wizard step-by-step.

• The Choose a Data Source dialog allows you to specify the source of your data.  Since we are running the Export wizard, the dialog will be displayed with the values already filled in as shown below (based on the database you right clicked to start the Export wizard):

  
  
  

• Click Next to proceed to the Choose a Destination dialog.

• The Choose a Destination dialog allows you to specify the destination data source for the data you are exporting.  There are quite a few options available for destinations; the additional inputs will vary based on the destination data source chosen. For our example, we will export our data to Excel then use this Excel spreadsheet as the source in the Import wizard demonstration later in the tutorial.  Fill in the dialog as follows:

• Click Next to proceed to the Specify Table Copy or Query dialog.

• The Specify Table Copy or Query dialog allows you to choose whether to export data by selecting tables and/or views from the data source or specifying a query to extract data.  Select Copy data from one or more tables or views as shown below:

• The Select Source Tables and Views dialog allows you to select the tables and views that you want to export.

• Click on Next

• The Complete the Wizard dialog shows a summary of the options that you have chosen for the export operation as shown below:

• Click on finish

IMPORT DATA

• Open SQL Server Management Studio Express and connect to your database.

• Right-click on your database and select Tasks > Import Data... from the side menu.

• The SQL Server Import and Export Wizard will open.

• Choose a data source for the data you want to import from the drop down.

• Enter the details of your MSSQL database, as follows:

• Destination: Select SQL Server Native Client 11.0 from the drop down menu.

• Server name: Enter the IP address of your MSSQL database. This information is shown within your Fasthosts control panel.

• Authentication: Select Use SQL Server Authentication and enter your database username and password. This is the same username and password you chose when you created your database.

• Database: Select your database from the drop down menu.

• Click Next.

• Select the tables you wish to import, then click Next.

• The wizard will then ask you to confirm if you wish to run the Import Immediately. Leave the option ticked and click Finish to import your data.

• You will receive confirmation that your data has been imported to your database.