table backup

Question

table backup

ramyours2003

SSChampion

Points: 12268
More actions
May 4, 2010 at 5:25 am

#140105

how can we take table backup in a database in sql 2005 , other than export/import wizard .
can any one send the query of taking the particular tables backup in a database ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You can't take a backup for a single database object, at most you can take a filegroup backup, but I'm sure this is not what you're after.

You can make copies of a table's data with import/export wizard, SSIS, BCP and many other tools.

Can you please give some more detail of your requirements?

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

If you bring in a third party tool like Hyperbac, you can access individual tables for restore of data from a database, file, or filegroup backup. Other than that, I don't know anything beyond what Gianluca said.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

ramyours2003 (5/4/2010)
how can we take table backup in a database in sql 2005 , other than export/import wizard .
can any one send the query of taking the particular tables backup in a database ?

I'm assuming that because you mention the export/import wizard that by backup, you mean writing the contents of the table to a file. The answer is that you can run OSQL (or similar in SQL 2005) from a DOS prompt or you can run BCP from a DOS prompt or, if the use of xp_CmdShell is allowed, you can use either from a stored proc.

You can also use some of the methods that Gianluca mentioned on this same thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Two other options:

Copy the table into newtable. This is something I like to do prior to making changes to it. It is not a backup per se, but is a recovery point prior to the changes being made.

An alternative is to move the table into it's own filegroup, perfrom filegroup backups. Then if you need to, you could restore the individual table via piecemeal restore.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

MannySingh SSChampion Points: 12465 More actions · Answer 5

you can use the select * into tablename_backup from tablename will give you a copy of the original table.

Maninder
www.dbanation.com