Question: How to Delete a Single Table From SQL Server Backup File (.bak)?
Answer: It is not possible by directly editing the backup file .bak.
I had actually heard this question during a recent interview, however, the only reason I have not included this question in the interview question and answer series is that I believe it is not worth that important. A good interviewer will never ask you this question in an interview as he knows that there is no value to this question in the real world.
I can totally see why sometimes users want to delete a particular backup from the backup file, however, it is not possible to do so at all. If you want to delete any table in backup, well, first you should delete that from your database and take backup.
If your intention is to reduce the size of the backup, I suggest you opt for compressed backup option available by default in SQL Server.
I guess, that’s it. I have nothing more to add in this subject. If you thought, I have a negative tone of this blog post, honestly that is not my intention at all.
Here are few related blog posts which you should read:
- Configure the Backup Compression Default Server Configuration Option
- How to Enable Backup Compression On by Default
- SQL SERVER – Playing with Backups and Compression
- Effect of Compressed Backup Setting at Server Level on Database Backup
Reference: Pinal Dave (https://blog.sqlauthority.com)
Oracle gives option to take backup without data (schema only), is there any way to do so in sql server?
You can export the table schema without data. There is an option for that in SQL server
Another way is restore db as a new from the backup, delete table and backup the new db )))
I got surprised , is it actually possible to delete a table from bak file .. after reading this . i just thanked god .. it should not happen even ..:)