With the increasing risk of data loss, every individual desires to maintain a secondary copy of important virtual-information. If you are looking forward to creating a copy of crucial SQL server data, you can use the import and export wizard to export your data sources and construct basic packages.
Steps of Exporting SQL Server Data
Here are the useful steps that you need to execute for exporting the SQL server data using the Import and Export Wizard.
Start SQL Server Import and Export Wizard
There are various ways by which you can start the wizard to export your SQL data. To start with, you can navigate to the All Programs menu under the Start Menu and click of Import and Export Data option available in Microsoft SQL Server tab. Alternatively, you can click right mouse button on the SSIS Packages folder in the SQL Server Data Tools and dig into SSIS Import and Export Wizard.In addition, you can also navigate to the SSIS wizard through the Project menu in the SQL Server Data Tools. If you are unable to use these ways, you can simply run DTSWizard.exe on the command prompt window. Once you start the wizard, you should proceed to the execution of the next step.
Select Data Source and Destination
On the wizard page, you need to select a source and destination for data. There are many sources that can be selected as the destination. Some of the common sources are OLE DB providers, Microsoft Office Excel, Microsoft Office Access,.NET Framework data providers, Flat File source and SQL Server Native Client providers.The configuration of options depends upon the selection of the source. Therefore, you need to set the authentication mode, database name, server name and file format depending upon the data source you choose.
Modify the Type of Destination
Once you complete entering the destination, it’s time to set the options. If your destination is SQL Server database, you can execute any of the four steps to proceed further. Firstly, you can indicate whether you want to create and set a new database along with its properties.
Secondly, you can choose to copy data from tables or views. You can create a Transact-SQL query if you want to replicate the results of source data. This can be done by entering the Transact-SQL query manually as well as by using a query that is saved to a specific file. The wizard opens and copies its content on the wizard page as soon as you select the file.
Thirdly, you can state if the data has been re-created after being dropped. You can also indicate if you want to enable identity inserts.
Lastly, you can specify if you want to insert or delete a row in an existing table.
However, if the destination set by you is a Flat File destination, you can indicate the row and column delimiter. Upon completion, you should move to the next step.
Select Tables
This is an optional step in the procedure of exporting SQL data. You can select one as well as multiple tables and make several modifications. First things first, if you select one table, you can make several changes in the form of alterations between source and destination columns or metadata of destination columns.
If you opt for multiple tables, you can update the metadata and indicate various things such as dropping and re-creating destination tables.
Once you are done with all the above-mentioned steps, you should save and test the package by running the program. If the package involves any problem, a red link will pop up in the screen. You can click on this link to review the problem and fix it to successfully export the data.
