table level backup

Question

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

Answer 1

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)

Answer 2

Thanks for your reply. But it is possible to take backup of a table as a dump file in oracle. Also it is possible to take backup of a table as a flat file in sql server. Is it possible to take it in a dump file in sql server?

Answer 3

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.

Answer 4

If I take table backup in CSV format then is it possible to restore it again from csv? what will happen if the table have any indexes? How can I restore indexes? In oracle, it is possible to restore table with indexes.

Answer 5

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.

Answer 6

Hi,

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.

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

Cheers,

Answer 7

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

Answer 8

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, select Tasks > Generate Scripts, and then in the 2nd page of the options wizard, make sure to select that you'd like the data scripted as well[[1]], and it will generate what amounts to a DUMP file for you.

1 http://updates.sqlservervideos.com/2009/01/wheres-that-been-all-my-life.html

Answer 9

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

Answer 10

really?? no code snip at all??

geez... it's not hard for any dba to throw up some code. i suppose it's me again.

here's a quick script to create a table backup copy ( not dump/export ), but a simple copy for anyone else is looking. it's fast ( number of rows pending of course ) make a note of column level objects is all you need to do before hand. could easily point this at say... a backup db location.

and here we go...

/******************************/

select top 0 * into MyTableCopy from MyOriginalTable

set identity_insert 'MyTableCopy' on

insert into MyTableCopy (col-1, col-2, col-3, etc... )

select * from MyOriginalTable

set identity_insert 'MyTableCopy' off

/******************************/

and thats it. too many nerd ninny's busy flaming away with RTFM crap, or "...not recommended" c'mon people; let'em work through it if there's a problem. thats the way i feel. disseminate the knowledge. forgive any typo's :) i'm out.

Mike L.