How to backup tables in SQL Server

Unlike other RDBMS platform SQL Server does not have an easy way to take a table level backup, often people use BCP or generate script method to take one or more table backup. In my previous blog posts, I have already demoed how to take a table backup with data using BCP. I have already authored a small tool called SQL Server Table Backup to give a face to BCP, using this tool you can easily take a table backup from your database without worrying about the commands and all. This is a kind of follow up post to those two posts. Recently when degrading a database from upper version to lower version created this script to migrate all tables with data in a single go.

Note: Do read the comments mentioned below each variables. Also, follow the screen captures after the script.

Backup:

  • Execute the script by providing the correct values to below mentioned variables

@flgTask = 0, @outPath = ‘Folder Path’, @instanceName = ‘SQL Instance Name’, @authType = 1, @userName = ‘your sqllogin’, @password = ‘Password’, @tableName = ‘Any Spcific’

  • Then, copy all the result set to a New Query Window → Turn on the SQLCMD mode in SQL Server → F5
  • Table data will be extracted to the given location.

bcp-backup-table-sqlindia

Restore:

  • Execute the script by providing the correct values to below mentioned variables

@flgTask = 1, @outPath = ‘Folder Path’, @instanceName = ‘SQL Instance Name’, @dbName = ‘Database Name’, @authType = 1, @userName = ‘your sqllogin’, @password = ‘Password’, @tableName = ‘Any Spcific’

  • Result set will have two columns as shown below, one containing RESTORE script using BULK INSERT method and the other one with BCP. You can use any of the method to restore the table data back.
  •  If you are using BCP method, then you have to turn on the SQLCMD Mode as shown in the above screen capture.

bcp-restore-table-sqlindia

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

%d bloggers like this: