Import/Export Wizard is an utility program you find in SQL Server (almost all versions) that can be used for Exporting or Importing data between SQL Servers; between SQL Server and a number of other data sources; or import/export data between data formats including databases, spreadsheets and text files.
DTSW_00 and DTSW_01.png
Export/Import utility can be launched from a database node in a SQL Server or from command line by typing in DTSWizard at command prompt.
The Welcome screen of the Wizard is displayed as shown.
DTSW_02
Let us consider a simple case of copying a database from SQL Server 2012 to SQL Server 2014. I am choosing this pair as I have the two servers on my computer.
DTSW_03
In particular, I will be exporting the pubs database in SQL Server 2012 to the named instance of SQL Server 2014.
DTSW_04
In the SQL Server Import Export Wizard (DTSW) I launched from command-line earlier, I click Next.
The Choose a Data Source page is displayed as shown.
DTSW_05
Click on the drop-down handle for Data source: as shown in the next image. You will see the various data sources handled by this wizard.
DTSW_06
We shall use the default data source, in this case the SQL Server Native Client 11.0. Note that the program automatically chose the named instance REGENCYPARK (SQL Server 2012). However, both the SQL Servers are accessible to this program. Since we are exporting from SQL Server 2012 to SQL Server 2014, RegencyPark server is correct. We will export to Everest server.
DTSW_07
Since both servers were originally installed to work with Windows Authentication accept the default. There is a <default> Database. Click on the handle and set it to pubs by choosing it in the drop-down. Do not worry if you do not have pubs. It should work with any other choice.
DTSW_08
With all the choices made so far, the Choose a Data Source window should appear as shown.
DTSW_09
Click Next to display the Choose a Destination screen as shown.
DTSW_10
Enter the following on this screen:
Destination: SQL Server Native Client 11.0
Server Name: Hodentek8\EVEREST
Authentication: Windows
Click on the handle for Database. The following drop-down is displayed.
DTSW_11
Click on the button New... to open the following Create Database window.
DTSW_12
Insert a name at the top. Herein pubs2. Accept all other defaults, and the database files that are going to be created are displayed inside the screen The OK button at the bottom becomes active. Click OK.
The Choose a Destination window appears as shown.
DTSW_13
Click Next> to display the Specify Table Copy or Query as shown.
DTSW_14
Accept the default, Copy data from one or more tables or views. Click Next> to display the Select Source Tables and Views with check boxes for each of the tables or views in the pubs database.
DTSW_15
Place check mark for the checkbox Source: and all the tables will be selected as shown.
DTSW_16
We will not edit the mappings (how the tables should go into the destination). Of course you can click Help to get to the help menu.
Click Next. The Save and Run Package is displayed. We accept the first option (Default) Run immediately. The other option is to save it as a SQL Server Information Services (SSIS) package.
DTSW_17
Click Finish>>| (you could also click Next> and look at some more details of what you have chosen to do so far). Complete the Wizard screen is displayed as shown:
DTSW_18
Click Finish on this screen. The program enters an execution stage and finally comes up with a screen announcing success and the number of rows transferred etc. as shown.
DTSW_19
Click on the Report button to display this drop-down list. You may choose an action you want to take.
DTSW_20
Now, go to the EVEREST server in SQL Server Management Studio for version 2014 and verify that pubs2 has been created and it has tables and data.
DTSW_21
That's it. Thanks for reviewing this post.
DTSW_00 and DTSW_01.png
Export/Import utility can be launched from a database node in a SQL Server or from command line by typing in DTSWizard at command prompt.
The Welcome screen of the Wizard is displayed as shown.
DTSW_02
Let us consider a simple case of copying a database from SQL Server 2012 to SQL Server 2014. I am choosing this pair as I have the two servers on my computer.
DTSW_03
In particular, I will be exporting the pubs database in SQL Server 2012 to the named instance of SQL Server 2014.
DTSW_04
In the SQL Server Import Export Wizard (DTSW) I launched from command-line earlier, I click Next.
The Choose a Data Source page is displayed as shown.
DTSW_05
Click on the drop-down handle for Data source: as shown in the next image. You will see the various data sources handled by this wizard.
DTSW_06
We shall use the default data source, in this case the SQL Server Native Client 11.0. Note that the program automatically chose the named instance REGENCYPARK (SQL Server 2012). However, both the SQL Servers are accessible to this program. Since we are exporting from SQL Server 2012 to SQL Server 2014, RegencyPark server is correct. We will export to Everest server.
DTSW_07
Since both servers were originally installed to work with Windows Authentication accept the default. There is a <default> Database. Click on the handle and set it to pubs by choosing it in the drop-down. Do not worry if you do not have pubs. It should work with any other choice.
DTSW_08
With all the choices made so far, the Choose a Data Source window should appear as shown.
DTSW_09
Click Next to display the Choose a Destination screen as shown.
DTSW_10
Enter the following on this screen:
Destination: SQL Server Native Client 11.0
Server Name: Hodentek8\EVEREST
Authentication: Windows
Click on the handle for Database. The following drop-down is displayed.
DTSW_11
Click on the button New... to open the following Create Database window.
DTSW_12
Insert a name at the top. Herein pubs2. Accept all other defaults, and the database files that are going to be created are displayed inside the screen The OK button at the bottom becomes active. Click OK.
The Choose a Destination window appears as shown.
DTSW_13
Click Next> to display the Specify Table Copy or Query as shown.
DTSW_14
Accept the default, Copy data from one or more tables or views. Click Next> to display the Select Source Tables and Views with check boxes for each of the tables or views in the pubs database.
DTSW_15
Place check mark for the checkbox Source: and all the tables will be selected as shown.
DTSW_16
We will not edit the mappings (how the tables should go into the destination). Of course you can click Help to get to the help menu.
Click Next. The Save and Run Package is displayed. We accept the first option (Default) Run immediately. The other option is to save it as a SQL Server Information Services (SSIS) package.
DTSW_17
Click Finish>>| (you could also click Next> and look at some more details of what you have chosen to do so far). Complete the Wizard screen is displayed as shown:
DTSW_18
Click Finish on this screen. The program enters an execution stage and finally comes up with a screen announcing success and the number of rows transferred etc. as shown.
DTSW_19
Click on the Report button to display this drop-down list. You may choose an action you want to take.
DTSW_20
Now, go to the EVEREST server in SQL Server Management Studio for version 2014 and verify that pubs2 has been created and it has tables and data.
DTSW_21
That's it. Thanks for reviewing this post.
No comments:
Post a Comment