We help IT Professionals succeed at work.

backup a table and restore

crishna1
crishna1 asked
on
Medium Priority
1,121 Views
Last Modified: 2008-03-28
Dear experts,

Can a single table be restored in Sql Server.

What is the best way to take a backup of a single table?

can the above be done without using Enterprise Manager, say using Querry Analyzer.

many thanks
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
>Can a single table be restored in Sql Server.
by default: no.

however, if that table is the only table in a filegroup, you can restore the filegroup which hence restores only that table.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
I think you mean how to save table outside the DB for migration or archive purpose.

you can export table along with data to a txt,csv,xl files ..... (using DTS) , you can use bcp, you can maybe also use openrowset ( I am not too sure about it)

but yes as angel pointed out you cannot do a "backup" as in DB backup of a single table
David ToddSenior Database Administrator
CERTIFIED EXPERT
Commented:
Hi Folks,

Isn't SQL 2005 supposed to be able to backup and restore a single table? Or is that only the filegroup as Angel mentioned.

Regards
  David
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
>Isn't SQL 2005 supposed to be able to backup and restore a single table? Or is that only the filegroup as Angel mentioned.
that's exactly the method: backup restore granularity is limited to filegroups...

Author

Commented:
Thanks for your feed back.

How do i restore a single table from the file group, command please.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you cannot restore a single table.
you CAN restore a single filegroup.

so IF and ONLY if your table is the ONLY one in a filegroup you can restore that filegroup and hence restore that table...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E3GAG
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.