SQLServer: Backup a table

 

Backup a Single Table in SQL Server

¹²³

To back up a single table in SQL Server, you can use various methods. Here are some common approaches:

Using SELECT INTO Statement

This method creates a new table with the same structure and data as the original table.

SELECT * INTO mytable_backup FROM mytable;

This command copies all rows from mytable to mytable_backup. Note that this will not copy indices, constraints, etc.

Using SQL Server Management Studio (SSMS)

1. Open SSMS and connect to the database instance.

2. Right-click on the database name, select Tasks, and then click on Generate Scripts.

3. Click Next on the pop-up.

4. On the Choose Objects window, select Select specific database objects, expand the Tables node, select the table you want to back up, and click Next.

5. On the Set scripting options window, click Advanced. Ensure to select the Schema and data option for the Types of data to script option and then click OK.

6. Select Open in new query window and click Next.

7. Review the selections on the Summary page and click Next.

8. Finally, it will generate the script with schema and data; click on Finish.

Using BCP (Bulk Copy Program)

BCP is a command-line utility that can be used to export data from a table.

DECLARE @table VARCHAR(128), @file VARCHAR(255), @cmd VARCHAR(512);

SET @table = 'AdventureWorks.Person.Contact';

SET @file = 'C:\\MSSQL\\Backup\\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.dat';

SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T';

EXEC master..xp_cmdshell @cmd;

This script exports the data from AdventureWorks.Person.Contact to a .dat file².

Important Considerations

• The SELECT INTO method does not copy indices or constraints.

• SSMS provides a more comprehensive backup including schema and data.

• BCP requires bulk import/export privileges.

Choose the method that best fits your needs based on your specific requirements.