SQL Server - How to restore the database except specific tables Can someone provide your insights, what is best option to restore the database from SQL Server (say prod to integration). I want to exclude specific tables during restore.


Using Script
Using SSIS
Using 3rd Party Tools.


If am using SSIS Integration Instance, does it involve licensing cost ? Some thoughts on this would help.
To exclude certain tables from your refresh of the integration environment, with a backup from production, you will need to make use of a staging\data cleansing environment.

The plan would be:


Backup production
Restore backup in staging 
Drop the unwanted tables
Backup staging
Restore backup in integration


This plan could easily be implemented with PowerShell (DBATools is well worth a look) or SSIS. 

If you like coding, go for PowerShell. If you like dragging icons around the screen, go for SSIS. Both can get the job done.