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.
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.
Note: “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.
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.