How to take backup & restore for one table?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » How to take backup & restore for one table?

How to take backup & restore for one table?

Rate Topic

Display Mode

Topic Options

Author

Message

sharma-691993

Posted Thursday, June 12, 2008 10:04 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, March 12, 2009 5:33 AM
Points: 68, Visits: 215

How to take backup & restore for one table?

Post #516069

MohammedU

Posted Thursday, June 12, 2008 12:58 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 1,190, Visits: 500

There is no option in sql server to backup the single table using backup command...but you can use DTS or BCP to copy the data and generate the table script...

MohammedU
Microsoft SQL Server MVP

Post #516207

tosscrosby

Posted Thursday, June 12, 2008 1:41 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316

That's about the only way in SQL2K. There are third party tools, like LiteSpeed that will allow you to restore a table (or other objects) individually but I don't recall if you can backup individual objects (I don't believe so). I agree with MohammedU, DTS or BCP are probably the easiest (read cheapest!) way to go.

-- You can't be late until you show up.

Post #516234

dba_ch

Posted Thursday, August 07, 2008 4:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 07, 2008 9:17 PM
Points: 5, Visits: 8

there is command to take back up of table

Post #548170

tosscrosby

Posted Thursday, August 07, 2008 6:31 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316

Not in native SQl 2000 or SQL 7. See the prior posts for the solutions for these platforms. Also, if you have a solution, please state it. Simply writing "there is command to take back up of table" isn't helping at all. Show a script or be more decriptive as to what the point is that you're trying to convey. Thanks.

-- You can't be late until you show up.

Post #548232

trh

Posted Friday, August 08, 2008 3:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, July 09, 2010 5:33 AM
Points: 34, Visits: 125

Can you put the table into it's own file group and just run a backup on that filegroup?

I'm addressing this more to the experts -

Tom

Post #548959

tosscrosby

Posted Friday, August 08, 2008 6:19 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316

Absolutely. That's also a good way of doing backups of VLDBs, especially if using native SQL. Allows you to slice it and dice it to meet your business recovery needs. We had a manufacturing environment where we knew 2/3 of our tables were relatively static and truly only needed to be backed up weekly. They were put into their own file group and backed up accordingly. But our inventory tables were constantly being updated and needed nightly backups. As the database grew, this was the best way to ensure we had what we needed. Then we went with a third party tool and it all became moot - full backups of everything nightly. Look up "BACKUP, filegroup backup" in BOL.

-- You can't be late until you show up.

Post #549048

dkumaresh 50476

Posted Wednesday, September 15, 2010 2:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 29, 2010 5:23 AM
Points: 1, Visits: 1

Right CLick on database
-Tasks
-Generate Scripts
-select a database
-Choose script options
(select the script data option set to true)
-and proceed further....

finally you can have the table structure coding along with the data.
(These steps for sql server 2008...you may use this tips for other version)

D. Kumaresh

Post #986086

karthik babu

Posted Wednesday, February 06, 2013 5:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 237, Visits: 287

Hi SSCrazy -
In that case, please explain about the filegroup restoration chain.. Smile

Post #1416437

GilaMonster

Posted Wednesday, February 06, 2013 6:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 37,729, Visits: 29,990

5 year old thread. Please post new questions in a new thread. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1416484

« Prev Topic | Next Topic »

Permissions