Post reply

Table Backup

  • February 15, 2011 at 4:32 am

    #233550

    Dear All,

    Can someone advise me how to take single table backup in SQLServer?

    If it's not possible,advise me is there any third party tools to proceed.

    Thanks in advance.

    Thanks and Regards,

    Ravi.

  • February 15, 2011 at 4:39 am

    #1286964

    No it's not possible. I'm not aware of any Third Party Products that do this.

    You could try restoring the whole database under a different name and then copying the rows from the newly restored database to the existing database, that is one possible way around your problem but I would test it first.

    Gethyn Elliswww.gethynellis.com

  • February 15, 2011 at 4:40 am

    #1286965

    Sorry mis-read the question. To backup an entire database you could use SSIS to export the data in a table to a file or another table, but there is no BACKUP TABLE command in SQL Server

    Gethyn Elliswww.gethynellis.com

  • February 15, 2011 at 5:23 am

    #1286977

    hey ..

    this is the one way to take Backup of Single table

    select * into <NewTableName> from <OldTableName>

  • February 15, 2011 at 6:29 am

    #1287013

    For a single table, export it is the answer. I work for a company that makes three different tools, all focused around backup and restore, and we don't backup single tables. None of the competition does to my mind either. For just one table, export. You have choices there, as was already mentioned, SSIS or a SELECT statement. You can also look at sqlcmd.exe, SMO through the language of your choice, PowerShell (with or without SMO), or the data migration wizard in SQL Server Management Studio (which is just SSIS with a pretty face).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 15, 2011 at 6:35 am

    #1287016

    remember some tables cannot be truly 100% "backed up" all by itself due to referential constraints....any foreign keys to lookup tables, tables for city/state/etc, would require those related tables to be included as well....in order to be 100%, right?

    exporting a copy to disk, or copying another table (the select into suggestion) or even copying to another database(which can then be backed up) are the way's I've done it, just as the other posters have mentioned.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 15, 2011 at 8:02 am

    #1287095

    Actually, you can back up a single table. But there are caveats.

    1) The table has to have a clustered index

    2) The table has to be on its own file & file group

    3) Then you backup the file / filegroup and you've backed up a single table.

    If 1 & 2 are not true, then your only options are to do as suggested above.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

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