Table-level backup
Sql ServerBackupDatabase TableSql Server Problem Overview
How to take table-level backup (dump) in MS SQL Server 2005/2008?
Sql Server Solutions
Solution 1 - Sql Server
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.
Solution 2 - Sql Server
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.
Solution 3 - Sql Server
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
Solution 4 - Sql Server
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
Solution 5 - Sql Server
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
Solution 6 - Sql Server
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.
Solution 7 - Sql Server
Create new filegroup, put this table on it, and backup this filegroup only.
Solution 8 - Sql Server
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.
Solution 9 - Sql Server
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.
-
I fetch new records (on the basis of a 'status' column) and transferred the data to destination. (Through 'Transform Data Task')
-
Then I just updated the 'status' column. (Through 'Execute SQL Task')
I had to fix the 'workflow' properly.
Solution 10 - Sql Server
Use SQL Server Import and Export Wizard.
- ssms
- Open the Database Engine
- Alt. click the database containing table to Export
- Select "Tasks"
- Select "Export Data..."
- Follow the Wizard
Solution 11 - Sql Server
> 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.
Solution 12 - 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.
Solution 13 - Sql Server
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
Solution 14 - Sql Server
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
Solution 15 - Sql Server
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.
Solution 16 - Sql Server
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:
- Install Handy Backup and create a new backup task.
- Select “MSSQL” on a Step 2 as a data source. On a new window, mark a database to back up.
- Select among different destinations where you will store your backups.
- On a Step 4, select the “Full” backup option. Set up a time stamp if you need it.
- Skip a Step 5 unless you have a need to compress or encrypt a resulting dump file.
- On a Step 6, set up a schedule for a task to create dumps periodically (else run a task manually).
- 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.