How to take table level backup in SQL Server

When you ask a DBA or developer that, what features they would most like to see added to the future versions of SQL Server, they will undoubtedly say, “The ability to backup of a single table to physical file system and recover a single table from an existing database backup without restoring it”. I being in the same race, would wait for these great features to see in future versions of SQL Server.

Now coming back to our topic, “how to take table level backup in sql server?” Recently I and one of my friend Yash Vora had an idea to develop a tool to take backup of a single table onto the file system and restore it back when needed. More often than not, we take backup of individual tables before doing any DML operations like Insert, Update and Delete on production or mission critical systems. There are couple of ways available in SQL Server like doing an INSERT INTO to another temporary table, or generating a script with data or BCP.

Copy SQL table from one server to another using BCPsource-code
Copy SQL table from one server to another using Powershell
Generate dynamic insert statements from a SQL Server table

BCP is a great method to take individual table backups without worrying about the datatypes or the amount of data present in the table. Here we have created a small windows GUI, which has the ability to take individual table backups and restore the same. This uses BCP behind the scene. Click here or on the button above to download the exe.

BACKUP

sql-server-table-backup-bcp-01

sql-server-table-backup-bcp-02Note: “Keep identity value” check box is not a mandatory field, if it is checked during backup then it will make sure the identity values are backed up as it is with other column data else only the regular data.

sql-server-table-backup-bcp-03

RESTORE

sql-server-table-backup-bcp-04

Note: “Keep identity value” check box is not a mandatory field, if it is checked during restore then it will truncate the whole table and restore the data with the identity that are in the table backup file.

sql-server-table-backup-bcp-05

sql-server-table-backup-bcpPlease leave us a feedback 🙂

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.

7 thoughts on “How to take table level backup in SQL Server

Leave a Reply

%d bloggers like this: