CODEWITHSUNDEEP
sqlsql-serversql-server-2008
EBS
EBS

Reputation: 1803

Backup a single table with its data from a database in sql server 2008

I want to get a backup of a single table with its data from a database in SQL Server using a script.

How can I do that?

Upvotes: 152

Views: 603518

Answers (8)

Ivan
Ivan

Reputation: 319

There are three methods for backing up MSSQL Server tables from the command line:

  1. mssql-scripter - this is a utility from Microsoft, that is installed using the Python packages manager (pip). The table can be specified in the --include-objects parameter:
mssql-scripter --server . --database Curseria --schema-and-data --include-objects Students > Students.sql
  1. sqlpackage.exe - this is the official utility to export data from Microsoft, it can be downloaded from the official website. The table to include in the backup should be specified using the /p:TableData= parameter, here is an example:
sqlpackage.exe /a:Export /SourceServerName:server-name /SourceDatabaseName:db-name /TargetFile:"c:/backup/file/path.bacpac" /SourceTrustServerCertificate:True /p:TableData=[dbo].[Students]
  1. Copy a table into a different temporary database and perform its backup:
CREATE DATABASE Students_table
SELECT * INTO Students_table.dbo.Students FROM OriginalDB.dbo.Students
BACKUP DATABASE Students_table TO DISK = 'C:\Backup\Students_table.bak'
DROP DATABASE Students_table

These commands can be executed from a script using sqlcmd utility. Sample.

P.S. Note, that to backup a separate table in general is bad practice. Perhaps it is better to transfer a table into a separate database.

Upvotes: 0

MGOwen
MGOwen

Reputation: 7239

SELECT * INTO mytable_backup FROM mytable

This makes a copy of table mytable, and every row in it, called mytable_backup.

(It will not copy any indices, constraints, etc. Just the structure and data).

(It will fail if you have an existing table named mytable_backup, so if you want to use this code regularly - for example, to backup daily or monthly - you'll need to run drop mytable_backup first. But consider avoiding this in those cases, and use SQL Server's proper official built-in backup tools instead. Those will provide better features. This is more for a temporary one-time backup before running a risky update script in test, etc).

Upvotes: 236

Kent Aguilar
Kent Aguilar

Reputation: 5338

You can use the "Generate script for database objects" feature on SSMS.

  1. Right click on the target database
  2. Select Tasks > Generate Scripts
  3. Choose desired table or specific object
  4. Hit the Advanced button
  5. Under General, choose value on the Types of data to script. You can select Data only, Schema only, and Schema and data. Schema and data includes both table creation and actual data on the generated script.
  6. Click Next until wizard is done

Upvotes: 88

Vijay Hulmani
Vijay Hulmani

Reputation: 979

There are many ways you can take back of table.

  1. BCP (BULK COPY PROGRAM)
  2. Generate Table Script with data
  3. Make a copy of table using SELECT INTO, example here
  4. SAVE Table Data Directly in a Flat file
  5. Export Data using SSIS to any destination

Upvotes: 44

Pankaj
Pankaj

Reputation: 685

You can create table script along with its data using following steps:

  1. Right click on the database.
  2. Select Tasks > Generate scripts ...
  3. Click next.
  4. Click next.
  5. In Table/View Options, set Script Data to True; then click next.
  6. Select the Tables checkbox and click next.
  7. Select your table name and click next.
  8. Click next until the wizard is done.

For more information, see Eric Johnson's blog.

Upvotes: 28

benjamin moskovits
benjamin moskovits

Reputation: 5458

Put the table in its own filegroup. You can then use regular SQL Server built in backup to backup the filegroup in which in effect backs up the table.

To backup a filegroup see: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-files-and-filegroups-sql-server

To create a table on a non-default filegroup (its easy) see: Create a table on a filegroup other than the default

Upvotes: 3

Simpa
Simpa

Reputation: 151

Another approach you can take if you need to back up a single table out of multiple tables in a database is:

  1. Generate script of specific table(s) from a database (Right-click database, click Task > Generate Scripts...

  2. Run the script in the query editor. You must change/add the first line (USE DatabaseName) in the script to a new database, to avoid getting the "Database already exists" error.

  3. Right-click on the newly created database, and click on Task > Back Up... The backup will contain the selected table(s) from the original database.

Upvotes: 2

wwmbes
wwmbes

Reputation: 304

To get a copy in a file on the local file-system, this rickety utility from the Windows start button menu worked: "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe"

Upvotes: 0

Related Questions