Taking backup of particular table from the Database

  • Dear All,

    I have XYZ database. I want to take backup of only 4 tables instead of entire database.

    Simarly. If i want to restore , i have to restore only those 4 tables.

    Please help me to solve this problem

    Regards

    Prakash

  • You can move those four tables in a different database file. take file/filegroup backup. You'll be able to restore them.

    http://msdn.microsoft.com/en-us/library/ms189906.aspx

    http://msdn.microsoft.com/en-us/library/ms179401.aspx

    .



    Pradeep Singh

  • ps (12/14/2008)


    You can move those four tables in a different database file. take file/filegroup backup. You'll be able to restore them.

    http://msdn.microsoft.com/en-us/library/ms189906.aspx

    http://msdn.microsoft.com/en-us/library/ms179401.aspx

    You'd still need to backup and restore the primary filegroup as well. Since primary contains the system tables, there's no way to restore a database without it. You'll also need transaction log backups if the filegroups aren't read-only.

    The only real way to backup just tables is to bcp the data to file and save those files somewhere.

    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
  • Need some clarification on this...

    -------------

    Assuming I've 3 filegroups, primary1, secondary1 and secondary2

    I take filegroup backups with log backups.

    If i need to restore all files from secondary1 filegroup(assuming this filegroup resided on a particular disk which crashed; primary1 and secondary2 filegroups are placed on different disks and are healthy),

    do i just restore the secondary filegroup along with logs backups

    OR

    do i have to restore primary, secondary1 and the log backups for this?



    Pradeep Singh

  • Is the database still online?

    SQL 2000, 2005 or 2008?

    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
  • Its 2000 and the database is online.

    Was browsing through sql server 2000 KB, and I think i got the answer....

    http://support.microsoft.com/kb/281122

    pasting an excerpt from the link..

    you have a database that consists of a primary filegroup, filegroup A and filegroup B. A table that resides on filegroup B is accidentally deleted. If you have filegroup and transaction log backups available, you can restore only filegroup B along with the primary filegroup to regain the deleted table.

    Even if the database is online; since i'm restoring the primary filegroup, i need to put the database in single_user mode right?

    This leads to another query....

    Backup of secondary1 filegroup gets over at 2PM and primary1 filegroup at 4 PM. Filegroup1 crashes at 5 PM.

    To restore filegroup1, i need all log backups from 2PM-5PM or from 4PM-5PM?



    Pradeep Singh

  • ps (12/15/2008)


    Even if the database is online; since i'm restoring the primary filegroup, i need to put the database in single_user mode right?

    It's never necessary to put a DB into single user mode to restore. You just need to be sure that no one's using it. The restore will need an exclusive database lock

    Backup of secondary1 filegroup gets over at 2PM and primary1 filegroup at 4 PM. Filegroup1 crashes at 5 PM.

    To restore filegroup1, i need all log backups from 2PM-5PM or from 4PM-5PM?

    From the earliest of the backups you're restoring. In this case, the secondary data file.

    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
  • Thanks Gail for your valuable comments 🙂

    I think i understand the backup fundamentals much better.



    Pradeep Singh

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

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