[sql-server] Table-level backup

How to take table-level backup (dump) in MS SQL Server 2005/2008?

Create new filegroup, put this table on it, and backup this filegroup only.

Handy Backup automatically makes dump files from MS SQL Server, including MSSQL 2005/2008. These dumps are table-level binary files, containing exact copies of the particular database content.

To make a simple dump with Handy Backup, please follow the next instruction:

1. Install Handy Backup and create a new backup task.
2. Select “MSSQL” on a Step 2 as a data source. On a new window, mark a database to back up.
3. Select among different destinations where you will store your backups.
4. On a Step 4, select the “Full” backup option. Set up a time stamp if you need it.
5. Skip a Step 5 unless you have a need to compress or encrypt a resulting dump file.
6. On a Step 6, set up a schedule for a task to create dumps periodically (else run a task manually).
7. Again, skip a Step 7, and give your task a name on a Step 8. You are finished the task!

Now run your new task by clicking on an icon before its name, or wait for scheduled time. Handy Backup will automatically create a dump for your database. Then open your backup destination. You will find a folder (or a couple of folders) with your MS SQL backups. Any such folder will contains a table-level dump file, consisting of some binary tables and settings compressed into a single ZIP.

Other Databases

Handy Backup can save dumps for MySQL, MariaDB, PostgreSQL, Oracle, IBM DB2, Lotus Notes and any generic SQL database having an ODBC driver. Some of these databases require additional steps to establish connections between the DBMS and Handy Backup.

The tools described above often dump SQL databases as table-level SQL command sequence, making these files ready for any manual modifications you need.

You can use the free Database Publishing Wizard from Microsoft to generate text files with SQL scripts (CREATE TABLE and INSERT INTO).

You can create such a file for a single table, and you can "restore" the complete table including the data by simply running the SQL script.

A free app named SqlTableZip will get the job done. Basically, you write any query (which, of course can also be [select * from table]) and the app creates a compressed file with all the data, which can be restored later.

Link: http://www.doccolabs.com/products_sqltablezip.html

Every recovery model lets you back up a whole or partial SQL Server database or individual files or filegroups of the database. Table-level backups cannot be created.

From: Backup Overview (SQL Server)

You probably have two options, as SQL Server doesn't support table backups. Both would start with scripting the table creation. Then you can either use the Script Table - INSERT option which will generate a lot of insert statements, or you can use Integration services (DTS with 2000) or similar to export the data as CSV or similar.

You can run the below query to take a backup of the existing table which would create a new table with existing structure of the old table along with the data.

select * into newtablename from oldtablename

To copy just the table structure, use the below query.

select * into newtablename from oldtablename where 1 = 2

If you are looking to be able to restore a table after someone has mistakenly deleted rows from it you could maybe have a look at database snapshots. You could restore the table quite easily (or a subset of the rows) from the snapshot. See http://msdn.microsoft.com/en-us/library/ms175158.aspx

You cannot use the BACKUP DATABASE command to backup a single table, unless of course the table in question is allocated to it's own FILEGROUP.

What you can do, as you have suggested is Export the table data to a CSV file. Now in order to get the definition of your table you can 'Script out' the CREATE TABLE script.

You can do this within SQL Server Management Studio, by:

right clicking Database > Tasks > Generate Script

You can then select the table you wish to script out and also choose to include any associated objects, such as constraints and indexes.

in order to get the DATA along with just the schema, you've got to choose Advanced on the set scripting options tab, and in the GENERAL section set the Types of data to script select Schema and Data

Hope this helps but feel free to contact me directly if you require further assitance.

This is similar to qntmfred's solution, but using a direct table dump. This option is slightly faster (see BCP docs):

to export:

bcp "[MyDatabase].dbo.Customer " out "Customer.bcp" -N -S localhost -T -E

to import:

bcp [MyDatabase].dbo.Customer in "Customer.bcp" -N -S localhost -T -E -b 10000

If you're looking for something like MySQL's DUMP, then good news: SQL Server 2008 Management Studio added that ability.

In SSMS, just right-click on the DB in question and select Tasks > Generate Scripts. Then in the 2nd page of the options wizard, make sure to select that you'd like the data scripted as well, and it will generate what amounts to a DUMP file for you.

I am using the bulk copy utility to achieve table-level backups

to export:

bcp.exe "select * from [MyDatabase].dbo.Customer " queryout "Customer.bcp" -N -S localhost -T -E

to import:

bcp.exe [MyDatabase].dbo.Customer in "Customer.bcp" -N -S localhost -T -E -b 10000

as you can see, you can export based on any query, so you can even do incremental backups with this. Plus, it is scriptable as opposed to the other methods mentioned here that use SSMS.

Use SQL Server Import and Export Wizard.

1. ssms
2. Open the Database Engine
3. Alt. click the database containing table to Export
4. Select "Tasks"
5. Select "Export Data..."
6. Follow the Wizard

BMC Recovery Manager (formerly known as SQLBacktrack) allows point-in-time recovery of individual objects in a database (aka tables). It is not cheap but does a fantastic job: http://www.bmc.com/products/proddocview/0,2832,19052_19429_70025639_147752,00.html

http://www.bmc.com/products/proddocview/0,2832,19052_19429_67883151_147636,00.html

Here are the steps you need. Step5 is important if you want the data. Step 2 is where you can select individual tables.

EDIT stack's version isn't quite readable... here's a full-size image http://i.imgur.com/y6ZCL.jpg

I don't know, whether it will match the problem described here. I had to take a table's incremental backup! (Only new inserted data should be copied). I used to design a DTS package where.

1. I fetch new records (on the basis of a 'status' column) and transferred the data to destination. (Through 'Transform Data Task')

2. Then I just updated the 'status' column. (Through 'Execute SQL Task')

I had to fix the 'workflow' properly.