How to take backup & restore for one table?

  • How to take backup & restore for one table?

  • 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

  • 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.

  • there is command to take back up of table

  • 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.

  • 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

  • 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.

  • 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

  • Hi SSCrazy -

    In that case, please explain about the filegroup restoration chain..:-)

    ______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

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

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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
  • This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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