Back up a single table in SQL Server

  • Comments 7

SQL Server doesn’t have an easy way to take a table backup, so I often use the bcp (Bulk Copy Program) to accomplish the same goal. I’ve mentioned this before, and someone told me when they tried it they couldn’t restore the table – ah the dangers of telling people half the information! I should have mentioned that you need to have a “format file” ready if the table does not exist at the destination. In my case I already had the table, in this person’s case they did not. The format file can be used to rebuild that table structure before the data is bcp’d in, and you can read more about it here: http://msdn.microsoft.com/en-us/library/ms191516.aspx

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.

Of course, there are other methods of moving a single table’s data in an out, including SQL Server Integration Services and even the older Data Transformation Services, or simply by using hte SQLCMD or PowerShell utilities to run a query and just save the output to a file. In fact, these days I’m using a PowerShell script to build INSERT statements from that query. That could also easily be modified to create the table structure (or modify one if needed) quite easily.

Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post
  • Hey Buck,

    What about in SSMS:

    Right-click database > tasks > generate sql scripts, then select the table and on the scripting options page, select advanced, choose "schema and data" in types of data to script.

    Done!

  • Hi Greg,

    I think this option is available only in SQL Server 2008. I dont think SQL Server 2005 or earlier versions have this feature.

  • Thanks Greg, liked the tip.

    Regards.

  • Y CAN'T we find the option of backng up a table

  • Hey Greg Low,

    Well, Generate scripts will work out. But, the problem by generating the script is if the Table is VERY LARGE, SSMS can't read the very large script.

    Better find an option to Backup a single table.

  • --Drop Database #Tmp

    Create Database #Tmp

    Declare @Str nVarchar(Max)

    Set @Str =''

    Select @Str = @Str + ' Select * Into #Tmp.dbo.[' + A.Name + '] From NewData.dbo.['  + A.Name + '] '

    From (Select Name From Sys.Objects Where (type_desc='User_Table' and Name Like 'C01%')

    Or (type_desc ='User_Table' and name In ('Users','Company'))) A

    Execute Sp_executesql  @Str

    BACKUP DATABASE #Tmp TO DISK = 'D:\TmpBack.bak'

  • --Drop Database #Tmp

    Create Database #Tmp

    Declare @Str nVarchar(Max)

    Set @Str =''

    Select @Str = @Str + ' Select * Into #Tmp.dbo.[' + A.Name + '] From NewData.dbo.['  + A.Name + '] '

    From (Select Name From Sys.Objects Where (type_desc='User_Table' and Name Like 'C01%')

    Or (type_desc ='User_Table' and name In ('Users','Company'))) A

    Execute Sp_executesql  @Str

    BACKUP DATABASE #Tmp TO DISK = 'D:\TmpBack.bak'

    Select * From Demo.Sys.Objects

Page 1 of 1 (7 items)