Can we take a Backup of particular Table in a Database

Question

Can we take a Backup of particular Table in a Database

sandeepivs

SSC-Addicted

Points: 494
More actions
July 20, 2011 at 5:44 am

#244534

HI Frds,
I have a table consists of 2 crores of Records. I need to take a backup of only that particular table along with the data not the whole database.
Is it possible in SQL Server 2005/2008.
Please Let me know ASAP.
Waiting for your Reply.
Thanks In Advance
Sandeep IVS

Viewing 15 posts - 1 through 15 (of 17 total)

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

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 1

No easy was to take table backup as we take full backup of a database. Use BCP utility to take table backup.

There’s another way to back up a table, and that’s to create a Filegroup and place the table there. Then you can take a Filegroup backup to back up a single table.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Stamey SSCertifiable Points: 5600 More actions · Answer 2

I agree with Sumanta. You need to create a separate file group, with at least one file in it, and rebuild your clustered index for that table in the separate file group, which will move the table into that file group. Then you can back up that one table by doing a file group backup.

Chris

Learning something new on every visit to SSC. Hoping to pass it on to someone else.

anil_kumar32 Ten Centuries Points: 1378 More actions · Answer 3

[font="Times New Roman"]

How a table can be transfered from one file group to another file group??

Regards,

Anil K

[/font]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

anil_kumar32 (7/20/2011)
[font="Times New Roman"]
How a table can be transfered from one file group to another file group??
Regards,
Anil K
[/font]

Recreate the clustered index on the new filegroup.

anil_kumar32 Ten Centuries Points: 1378 More actions · Answer 5

[font="Times New Roman"]

Its true that when we create cluster index in some other filegroup then table data would be in that file group also. but during backup table and index must be backed up as a single unit. In that case we have to back up two filegroup,this is not what we want.

Let me know if i am wrong.

Regards,

Anil K

[/font]

Jannie-186227 SSCrazy Points: 2319 More actions · Answer 6

Hi

In order for you to restore that filegroup the primary filegroup needs to exist, in this case it contains all the other 100 tables 🙂

So you have create a table backup on it's own which still has the dependency of the rest of the DB.

If it's a critical table, called me simple but sometimes simple is good.

Create new db, select into etc.

backup db.

Cheers

Jannie

sandeepivs SSC-Addicted Points: 494 More actions · Answer 7

Hi Friends,

Thanks for your Replies..

I don't have permissions to take backup of a database...

In this Case, How do I take a backup of a table?

I don't want to take whole the database backup.

Kindly, help me how to solve this issue ASAP.

🙂

Thanks In Advance,

Sandeep IVS

Jannie-186227 SSCrazy Points: 2319 More actions · Answer 8

select * into <table>_Backup from <table>

where table is your table

sandeepivs SSC-Addicted Points: 494 More actions · Answer 9

What does that command do Mr. Jannie?

Let me know clearly?

Jannie-186227 SSCrazy Points: 2319 More actions · Answer 10

Do you have security access to Bol?

I know it's used to select into tables, copy all the data, and thus a backup.

But books online might be able to give you more info.

Cheers

Jannie

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

anil_kumar32 (7/20/2011)
[font="Times New Roman"]
Its true that when we create cluster index in some other filegroup then table data would be in that file group also. but during backup table and index must be backed up as a single unit. In that case we have to back up two filegroup,this is not what we want.
Let me know if i am wrong.
Regards,
Anil K
[/font]

The clustered index IS the table. I'm not sure about the rest of the index, maybe you'd have to move them also.

Once everything is in 1 filegroup you can just backup that filegroup.

solucien SSC Enthusiast Points: 146 More actions · Answer 12

Check this link for an explanation

http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

SELECT INTO

This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.

Example

USE AdventureWorks

GO

----Create new table and insert into table using SELECT INSERT

SELECT FirstName, LastName

INTO TestTable

FROM Person.Contact

WHERE EmailPromotion = 2

solucien SSC Enthusiast Points: 146 More actions · Answer 13

It basically creates a new table with the data from a another table. See example