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:
- a SSIS job that copies certain stuff (using Import/Export Wizard)
- replication (snapshot or transactional)
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!