Table level backup

Question

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

Answer 1

You cannot use the BACKUP DATABASE to back up a single table, unless, of course, the corresponding table is assigned to it by FILEGROUP .

What you can do, since you suggested exporting the table data to a CSV file. Now, to get the definition of your table, you can "Script out" a CREATE TABLE script.

This can be done in SQL Server Management Studio using:

right click Database> Tasks> Generate Script

You can then select the table in which you want to execute Script, and also include any related objects, such as constraints and indexes.

to get DATA along with only schema , you need to select Advanced on the scripting options tab, and in the GENERAL section set Types of data to script select Schema and Data

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

Answer 2

I am using bulk copy utility to create table level backups

for export:

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

for 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 request, so you can even do incremental backups with this. This is also a scenario, unlike the other methods mentioned here that use SSMS.

Answer 3

Here are the steps you need. Step 5 is important if you want data. Step 2 is the selection of individual tables.

the EDIT version is not fully readable ... here's a full-sized image http://i.imgur.com/y6ZCL.jpg

Answer 4

You can run the query below to back up an existing table, which will create a new table with the existing structure of the old table along with the data.

 select * into newtablename from oldtablename 

To copy only the table structure, use the following query.

 select * into newtablename from oldtablename where 1 = 2 

Answer 5

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

for export:

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

for import:

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

Answer 6

If you are looking for something like MySQL DUMP , then the good news is: SQL Server 2008 Management Studio has added this ability.

In SSMS, simply right-click on the appropriate database and select Tasks> Generate Scripts. Then, on the 2nd page of the parameter wizard, make sure to select that you would like to use data scripts as well , and it will generate what constitutes a DUMP file for you.

Answer 7

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

You can create such a file for one table, and you can “restore” the entire table, including data, by simply running the SQL script.

Answer 8

Create a new filegroup, put this table on it, and back up only this filegroup.

Answer 9

I do not know if this will be consistent with the problem described here. I had to do a step-by-step backup of the table! (You only need to copy the newly inserted data). I used to create a DTS package where.

• I take new records (based on the status column) and transfer the data to the destination. (Via the "Data Conversion Task")

• Then I just updated the status column. (Via Run SQL Task)

I needed to correct the "workflow" correctly.

Answer 10

Each recovery model allows you to back up a full or partial SQL Server database or individual database files or filegroups. Table level backups could not be created .

From: Backup Overview (SQL Server)

Answer 11

Use the SQL Server Import and Export Wizard.

• SSMS
• Open the database engine
• Alt click the database containing the table to export
• Select Tasks
• Select "Export Data ..."
• Follow the wizard

Answer 12

You probably have two options: SQL Server does not support backing up tables. Both will begin with table creation scripts. Then you can use the Script Table - INSERT option, which will generate many insert statements, or you can use Integration services (DTS since 2000) or similar to export data like CSV or similar.

Answer 13

BMC Recovery Manager (formerly known as SQLBacktrack) allows you to instantly recover individual objects in the database (aka tables). It's not cheap, but it 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 14

If you want to restore a table after someone mistakenly deleted rows from it, you could take a look at the database snapshots. You can easily restore a table (or a subset of rows) from a snapshot. See http://msdn.microsoft.com/en-us/library/ms175158.aspx

Answer 15

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

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

Answer 16

Handy Backup automatically creates dump files with MS SQL Server, including MSSQL 2005/2008. These dumps are table-level binaries containing exact copies of the specific contents of the database.

To make a simple dump with Handy Backup, follow these instructions:

• Install Handy Backup and create a new backup task.
• Select "MSSQL" in step 2 as the data source. In the new window, mark the database for backup.
• Choose one of the different destinations where you will keep your backups.
• In step 4, select the "Full" option. Set the timestamp if you need it.
• Skip step 5 if you do not need to compress or encrypt the resulting dump file.
• In step 6, configure the task schedule for dumping periodically (otherwise start the task manually).
• Skip Step 7 again and give your task a name in Step 8. You have completed the task!

Now start a new task by clicking the icon in front of its name or wait for the scheduled time. Handy Backup will automatically dump for your database. Then open the backup location. You will find a folder (or a couple of folders) with MS SQL backups. Any such folder will contain a dump file at the table level, consisting of several binary tables and settings, compressed into one ZIP.

Other databases

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

The tools described above often duplicate SQL databases as a series of SQL commands at the table level, which makes these files ready for any manual changes you need.