Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 646
  • Last Modified:

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.
0
crcsupport
Asked:
crcsupport
  • 3
  • 3
4 Solutions
 
Scott PletcherSenior DBACommented:
On the primary server, move the trouble table(s) to a separate filegroup(s).

On the testing server, do a partial restore, omitting the trouble filegroup(s).  If required, you could then run a script to create an empty version of the trouble table(s), to avoid "object not found" errors.
0
 
HuaMinChenBusiness AnalystCommented:
You can schedule a job which is calling a Stored proc, to copy the relevant tables.
0
 
crcsupportAuthor Commented:
I'm not allow to change anything in source database.

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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
A filegroup is completely transparent to everything else in SQL.  If you can't change that, you're in for a real pain as you spend huge amounts of time transferring data and adjusting copy code to account for new tables, columns, etc..

[Btw, if you can't change "anything" in the source database, how were you going to add Replication, even if all PKs were there?]
0
 
crcsupportAuthor Commented:
I can add/remove scripts I did, but Changing database settings and schema of database is not allowed.
0
 
Scott PletcherSenior DBACommented:
Ouch!  Good luck: Trust me, it will be royal pita having to do this outside of filegroups.
0
 
crcsupportAuthor Commented:
So I think creating a filegroup is the best option... ah
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now