Post reply

Can we backup a single table in SQL Server 2008?

  • August 23, 2009 at 1:41 am

    #209727

    Hi,

    I just like to know that can we backup a single table in SQL Server 2008?

  • August 23, 2009 at 2:54 am

    #1043330

    No.

    SQL backups are database, filegroup and file. You could, technically, put the table into a filegroup by itself and back that up, but when restoring you'd still need the primary filegroup of the database

    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

  • August 23, 2009 at 5:40 am

    #1043339

    you could do a logical save of data at the table level by using the bcp utility or an SSIS export.

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

  • August 23, 2009 at 6:18 am

    #1043343

    - another alternative is to just copy that table into another database and create a full backup of that db.

    I would not suggest to do it this way all the time, but just in some exceptional occasions.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • August 23, 2009 at 9:12 am

    #1043365

    I like ALZDBA's suggestion, and hadn't thought of that. It adds an extra step to backing up and restoring the db, but that might be a good way to track the backup and restore.

    Note that the import/export wizard could help you here, either with moving the data to some flat file, or even moving it to a new database that you back up and restore.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 25, 2009 at 7:24 pm

    #1044628

    Steve Jones - Editor (8/23/2009)

    Note that the import/export wizard could help you here, either with moving the data to some flat file, or even moving it to a new database that you back up and restore.

    This would require an SSIS package to be executed. I believe that there are ways to do that from T-SQL but I've never tried them.

    I would execute a small T-SQL script from the maintenance plan (SQL Server 2005 and later) using the "Execute T-SQL Statement Task".

    [font="Courier New"]use [BackupDB]

    go

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BackupDB].[dbo].[MyTable]') AND type in (N'U'))

    DROP TABLE [BackupDB].[dbo].[MyTable]

    select * into [BackupDB].[dbo].[MyTable] from [MyDB].[dbo].[MyTable][/font]

    I think that this would be faster as well.

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

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