How can I backup table data to a local storage in SQL Server?

2 years ago
Noah Thompson
2 minutes

To back up table data in SQL Server to a local location, you can use the following methods:

  1. Exporting with SQL Server Management Studio (SSMS):
  2. Open SSMS and connect to the desired database.
  3. In the Object Explorer, expand the databases, expand the tables, and select the table you want to backup data from.
  4. Right-click on the table, select “Tasks” -> “Export Data”.
  5. On the “Select Data Source” page, choose “SQL Server Native Client” as the data source and select the target server and database.
  6. On the “Select Target” page, choose “Flat File” as the target, and specify the file path and name where you want to save the backup data.
  7. On the “Save and Run Package” page, you have the option to save as an SSIS package or run the export task immediately.
  8. After completing the settings, click the “Finish” button to start exporting the data to a local file.
  9. Utilize the bcp command-line utility in SQL Server.
  10. Open the command prompt and navigate to the “Tools\Binn” directory under the installation directory of SQL Server.
  11. Backup data to a local file by running the following command: bcp database name.schema name.table name out output file path -S server name -T -n. In this command, the database name is the name of the target database, the schema name is the schema where the table is located (usually dbo), the table name is the name of the table to be backed up, the output file path is the file path to save the backup data, the server name is the name of the target server, -T option indicates to use Windows authentication login, -n option indicates to export data in binary format.
  12. Insert data into a local table using the INSERT INTO SELECT statement.
  13. Create a table locally with the same structure.
  14. Select and insert data from the source database into the local table using the following statement: INSERT INTO local_table_name SELECT * FROM source_database_name.schema_name.table_name

    Where local_table_name is the name of the local table where data will be inserted, source_database_name is the name of the source database, schema_name is the schema where the table is located (usually dbo), and table_name is the name of the table to back up.

bannerAds