Post reply

Can we take a Backup of particular Table in a Database

  • 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

  • July 20, 2011 at 6:04 am

    #1355501

    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//

  • July 20, 2011 at 6:19 am

    #1355515

    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.

  • July 20, 2011 at 6:59 am

    #1355554

    [font="Times New Roman"]

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

    Regards,

    Anil K

    [/font]

  • July 20, 2011 at 7:04 am

    #1355558

    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.

  • July 20, 2011 at 10:00 pm

    #1356138

    [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]

  • July 20, 2011 at 10:57 pm

    #1356147

    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

  • July 20, 2011 at 11:27 pm

    #1356150

    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

  • July 20, 2011 at 11:56 pm

    #1356154

    select * into <table>_Backup from <table>

    where table is your table

  • July 21, 2011 at 12:12 am

    #1356161

    What does that command do Mr. Jannie?

    Let me know clearly?

  • July 21, 2011 at 12:37 am

    #1356163

    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

  • July 21, 2011 at 5:13 am

    #1356261

    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.

  • July 22, 2011 at 1:47 pm

    #1357282

    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

  • July 22, 2011 at 1:48 pm

    #1357283

    It basically creates a new table with the data from a another table. See 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

  • July 22, 2011 at 1:54 pm

    #1357286

    If you only need to back up a specific table, bcp Utility is the easier way to do it. Check the bcp Utility at http://msdn.microsoft.com/en-us/library/ms162802(v=SQL.90).aspx.

    --------------------------------------------------------------
    DBA or SQL Programmer? Who Knows. :unsure:

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

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