aeedfabc   
   XPost: microsoft.public.sqlserver.server   
   From: esquel@sommarskog.se   
      
   Bill E. Hollywood, FL (behrreich@compuserve.com) writes:   
   > 1. Store files in the server file system and include a path or partial   
   > path to the file in a varchar type column.   
   > 2. Store files in the database in a table that includes a   
   > varbinary(max) or other acceptable column type.  Options here include   
   > using filestream as well as using a separate filegroup for BLOB   
   > storage.   
   > 3. Using a separate database on the same SQL Server for the BLOBs.   
   > 4. Other options?   
   >   
   > I have experience with 1 and 2 and I understand some of the benefits   
   > and drawbacks of each.  However, I have another requirement--that I be   
   > able to backup and restore the database WITHOUT the BLOBs and still   
   > have a working database that I can use for development.  Option 1   
   > gives me this but I'm not convinced that I can easily do that with   
   > option 2 even if BLOBs are in a separate filegroup and I do filegroup   
   > restores (please correct me if I'm wrong).   
      
   I certainly like option 3 better than option 1. BACKUP/RESTORE on two   
   databases is easier than having to restore a database and a file system.   
   And with option 3, you could build the database on views, so it looks   
   that the data is in one table, permitting you to merge the databases   
   later on if you wish.   
      
   As for the second option, I don't what happens if you restore the database   
   without the BLOB filegroup. In the best of worlds, you should be able   
   to access the rest of the data, although I don't know whether it actually   
   works that way. So much is clear, if you try to access the BLOB data,   
   you will lose.   
      
   Then again, if you want database without the blobs for development,   
   what will you have instead of the blobs? Just have a fixed set of documents   
   and only work with these for your test cases?   
      
   How big do you expect the production database to be?   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)