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?

  • Wouldn't the approach in this question work for you? – Martin Smith Feb 4 '14 at 13:51
  • Thanks for the link. It is the same scenario but I couldn't restore from a partial database but I can now using RESTORE WITH MOVE. We would like access to our document metadata, so the approach I'm going to try is single database, split the document table in 2, one with all the metadata and the other with the filestream stuff and amend our SQL queries slightly. Then we can have DRI and partial backup/restore when required. Thanks. – John J Smith Feb 5 '14 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.