I have two SQL 2008 database servers, one primary and one testing.
Everyday, we backup a DB from primary and restore to testing so that we can run reporting and others. The problem is, one of tables, which is not needed for reporting, is growing too fast, resulting the daily back/restore takes longer and wasting disk space on testing server.
I was looking into Transactional Replication to replicate all other tables, but not the trouble table, but half of tables I want to set replication doesn't have primary key in it, so I can't use Transactional Replication. I'm not allowed to change table schema of the source database in primary server. Is there a way to solve this situation?
In short, I'm trying to copy a database from server A to server B excluding a table and it will be performed as scheduled time once a day.