Export SQL Server data by using SQL Server Management Studio

Updated at:

SQL Server Management Studio (SSMS) is an integrated environment used to access and manage SQL Server database components. This topic describes how to export SQL Server database data to a local host.

Prerequisites

  • SQL Server Management Studio is installed. You can download the installation package from the official Microsoft website.

    Note

    This topic uses SQL Server Management Studio 18.10 as an example. The user interface (UI) may vary in other versions.

  • The SQL Server database must contain data to export, such as tables or views.

Background information

You can use the exported data as a backup to restore a Web Hosting database, or import it into a new database to replicate the original schema.

This topic uses exporting tables from a SQL Server 2008 database as an example. The procedure is similar for other database objects.

Procedure

  1. Obtain database information.
    1. Log on to the Web Hosting management page.
    2. Find the target Web Hosting instance and click Manage in the Actions column.
    3. In the left-side navigation pane, click Database Information.
    4. Note If you have forgotten the database password, reset it. For more information, see Reset Web Hosting database password.
      获取windows数据库
  2. Connect to the Web Hosting database using SQL Server Management Studio.

    1. On your local host, open SQL Server Management Studio.

    2. In the Connect to Server dialog box, enter the connection information and click Connect.

      The following table describes the parameters.

      Parameter

      Description

      Server type

      Keep the default value, Database Engine.

      Server name

      Enter the database address of the Web Hosting instance.

      Authentication

      Keep the default value, SQL Server Authentication.

      Login

      Enter the database username of the Web Hosting instance.

      Password

      Enter the database password of the Web Hosting instance.

      After a successful connection, the database node for your Web Hosting instance appears in the left-side navigation pane.

  3. Export tables from the SQL Server database to your local host.

    This section provides examples for exporting data as a script file and as an Excel file.

    • Export as a script file

      1. In Object Explorer, right-click the target database (for example, hds14190_db) under the Databases folder, and then select Tasks > Generate Scripts....

      2. On the Introduction page of the Generate Scripts wizard, click Next.

      3. In the Select Objects section, select Select specific database objects(O), and select the checkbox next to Tableicon, then click Next(N). On the Select Objects page, select Select specific database objects(O), expand the Table node and select the target tables (for example, dbo.test and dbo.test1), then click Next(N).

        Note

        This step provides an example of exporting a table. You can also click Select All (S) to export all objects from the database at once, such as tables, views, stored procedures, and user-defined functions.

      4. On the Set Scripting Options page, configure the output type and destination for the script.

        1. Select Save as script file. In the File to be generated section, select One script file per object, specify a destination path, select Overwrite existing file, and set Save as to Unicode text. Then, click Advanced.

        2. In the Advanced Scripting Options dialog box, set Types of data to script to Schema and data, and then click OK.

        3. Click Next.

      5. On the Summary page, review your selections, and then click Next.

      6. After the table script is published and saved successfully, click Finish(F). Wait for the script generation to complete. After all operation results show Success, click Finish(F).

        You can view the exported table scripts in the specified directory on your local host.

    • Export as an Excel file

      1. In Object Explorer, right-click the target database (for example, hds14190_db) under the Databases folder, and then select Tasks > Export Data....

      2. On the Welcome page of the SQL Server Import and Export Wizard, click Next.

      3. On the Choose a Data Source page, configure the data source parameters, and then click Next.

        Key parameters:

        Parameter

        Description

        Data source

        Select SQL Server Native Client 11.0.

        Server name

        Enter the database address of the Web Hosting instance.

        Authentication

        Select Use SQL Server Authentication.

        • User name: Enter the database username of the Web Hosting instance.

        • Password: Enter the database password of the Web Hosting instance.

        Database

        Enter the database name of the Web Hosting instance.

      4. On the Choose a Destination page, configure the destination parameters, select the First row has column names checkbox, and then click Next.

        Key parameters:

        Parameter

        Description

        Destination

        Select Microsoft Excel.

        Excel file path

        Click Browse... to select a destination path for the file.

        Excel version

        Select the required Excel version. This topic uses Microsoft Excel 97-2003 as an example.

      5. On the Specify Table Copy or Query page, select Copy data from one or more tables or views, and then click Next.

      6. On the Select Source Tables and Views page, select the checkbox checkbox next to the tables that you want to export in the Source area. After the corresponding table name appears in the Target area, click Next. On the Select Source Tables and Views page, select the tables that you want to export (for example, [dbo].[test]), and then click Next.

      7. On the Review Data Type Mapping page, set both On Error (Global) and On Truncation (Global) to Ignore, and then click Next.

      8. On the Save and Run Package page, click Next. On the Complete the Wizard page, click Finish.

      9. After the page displays Succeeded, click Close. The wizard is complete. An Execution successful message is displayed, the status of all steps is Success, and the data has been successfully transferred to the destination file.

        You can open the Excel file in the destination path on your local host to view the exported data.