Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Wouldn't the approach in this question work for you? –  Martin Smith Feb 4 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 at 13:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.