We have a production database which is filestream enabled and has grown, within a year, to about 20Gb as it stores documents. On many occasions we need to bring the live data back to UAT or Dev environments for development or testing purposes.
This takes a long time and in all cases we don't need the documents so our plan was to split the database into two databases, one containing all the 'normal' tables and the other the filestream data. (Partial backup doesn't seem to be an option as restoring only the non-filestream data doesn't work).
To ensure referential integrity between the two databases, the intention was to use triggers on the 'parent' table and triggers on the 'child' table (in the filestream database) but triggers do not seem to be supported with filestream tables (When I try and do an insert I get the error:
The target table <Table> of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
Also, even if managed to ensure that no 'child' record could be inserted without a corresponding 'parent' record, my tests, so far, have shown that I could potentially insert a parent record and if the child insert fails, then I'm left with a parent record containing all the metadata but no filestream content for that record.
What are current best practices for ensuring RI between two databases, where one is filestream enable or is there a better approach to our issue?
