backup table

Question

backup table

shanila_minnu

Hall of Fame

Points: 3465
More actions
June 17, 2009 at 3:54 am

#133236

how can we take a backup of table in a database

Viewing 8 posts - 1 through 7 (of 7 total)

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

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

Hi,

There is no command for taking the backup of a single table in sqlserver. But if you want you can take the backup by creating other dummy table.

1. select * into dummytable from orgtable (or)

2. script out the table so that you can have the table creation script and bcp out the data.

Thanks

Chandra Mohan N

[font="Verdana"]Thanks
Chandra Mohan[/font]

CrazyMan SSChampion Points: 12560 More actions · Answer 2

CrazyMan

SSChampion

Points: 12560

June 17, 2009 at 5:22 am

#1010786

you can also use DTS or SSIS 🙂

manishankar_83 Old Hand Points: 391 More actions · Answer 3

Hi,

You can create a dummy DB and o a select * into table_name from TABLE_NAME

Once the data has been moved, you can then take a backup of the dummy DB and drop the DB

I haven't tried this, but guess you can create another FG on the existing DB and move this table to this FG and back it up. (Not sure though about restore considerations)

Thanks,

Mani

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 4

We cannot restore the filegroup backup from one database to another ..

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

SQL Severe DBA Hall of Fame Points: 3579 More actions · Answer 5

Hi ,

Yes you can take Table Backup , two main steps involved

1. Taking backup of Data in the table :-

a) By using Flat file or Excel sheet you can do that

b) By using Table to Table transfer

2) Creating same table with Original Table Schema so that you inseret the data taken from step 1 .

hope it helps !!

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 6

As of others have mentioned, you can put the table on a separate file or filegroup for backing up.

Of course, if you're trying to restore this table to a different database, that's another matter completely. The original question was about backing up only a table and that's all my above answer covers.

Other posters have covered your other options.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Russel Bell Old Hand Points: 305 More actions · Answer 7

Regarding post above with these options:

1. select * into dummytable from orgtable (or)

2. script out the table so that you can have the table creation script and bcp out the data.

The first option is the simplest because it does not require any scripting. The resulting table has the same columns and datatypes and data content as the source table (you can select just specific columns if you choose). The downside is that the table does not have a PK or indexes. It is an easy way to preserve a copy of the data, but you can not just rename the tables to restore - you could lose PK and indexes.

The second option (scripting it out and changing the table name and any constraint names) will also preserve the PK constraint info. You can then do "INSERT INTO tableA SELECT * FROM tableB" to copy the data.

Both these options make copies of the table data in the same database. If you're trying to back up the data to a location outside the database, then script the table/index creates and use "bcp" command line utility with "out" option to copy the data. To restore the table you would then drop run the create and use "bcp" with "in" option.