I have a client who has been promised that he will get a regular copy of the database behind the application we are hosting for him.
The copy is in fact a backup (DB_EXPORT.BAK) that he downloads through SFTP.
(I know, I did not make that promise). I do not want to give him the whole with all the proprietary stored procedures, functions, users and other stuff. I want to give him a slimmed down version of that database with most tables, only selected sp's, some functions, no users and so on. As I see there are two ways to do this:
The thing is: the original (DB1) AND the copy (DB_EXPORT) will be hosted on the same server. So using replication feels a bit awkward (publishing to yourself?) but it does give an easy interface for configuring which articles to replicate. Using a SSIS package feels more logical but is actually hard to change.
What can you say about this? Is there a better way for doing this? I am looking for a way that will allow people who just about understand SQL server wil be able to understand.
Thanks for thinking with me!
Not sure if this is the best answer, but I would go with snapshot replication per our discussion, and your avoidance of TSQL scripting.
Replication is relatively simple to setup, but can be a nightmare to troubleshoot (esp. transactional). Often times, its easier to completely delete the publication/subscription and rebuild.
On that note, you can fully script replication configurations -- if someone else has to maintain this, it may be as simple as you scripting out the replication removal (pub and sub removal), and scripting out the replication build-out. All they'd have to do is run the drop/build scripts and it's done.
You can also alter the scheduled job to run the backup immediately following the snapshot generation.