SQL 2008, copying database daily excluding a few tables.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can add/remove scripts I did, but Changing database settings and schema of database is not allowed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So I think creating a filegroup is the best option... ah
ASKER
Can you give me starting sql script which will copy only tables that I need. I'm not really good at database so I like to do more research basing on the script as starting point.