8

I use SQL Server and have a huge database with two filegroups:

  • Primary: Which contains all the data except the large files (1MB+)
  • FILESTREAM (read/write): Which contains the large files

Now, the backup scenario is:

  • Each Friday get a full backup (2 A.M)
  • Each day on week except Friday get a differential backup (2 A.M)

Since the database is large, and it is in production on a remote server, whenever I want to bring the database to my local environment to create a test database (weekly), I have to bring both the primary and the filestream.

I would like to be able to change the way the backups and restores are done, in such a way that only had to bring the primary filegroup, ignoring the filestream. By this way, every week I would only bring the primary filegroup, and not all information that suppose the filestream.

I think there can be a lot of problems, and all filestream references can be lost when accessing the files. I would like to know if is it possible to modify the content of all filestream columns when performing a backup, or use a different filestream hosted in the test environment. Also, I've heard about Piecemeal Restore of only some Filegroups, but I have many doubts on how to carry it out.

Question 1: can I have this scenario?

Question 2: is it a good idea to have only one Full backup and bring differential backups/transaction logs to test environment?

Question 3: can I have better scenario to backup and restore?

I'm all ears to recommendations. If you have any example case, please show me with a T-SQL query.

Improve this question
2
  • Filestream Overview tells us explicitly you can exclude FILESTREAM groups from your backups. The TSQL implementation would be awesome to see, though
    – clifton_h
    Commented Sep 9, 2017 at 15:56
  • Yes, you can exclude FILESTREAM from your backup, but in the restore process you will need to provide it. I think that this is always a need when your filestream group is read-write. This means that you can do a partial backup only of your data and another backup including read-write filegroups (which contains the files)
    – Mario Navarro Claras
    Commented Sep 11, 2017 at 12:41

1 Answer 1

Reset to default
1

I would like to know if is it possible to modify the content of all filestream columns when performing a backup

No.

I would create my test database with the same schema as the production database, and then write a custom script to sync the data in the primary filegroup (this is fairly easy as your production system is the source, so the sync is only in one direction).

Then with the filestream data, I would stub the files, which could be done any number of ways. Off the top of my head, I'd somehow generate zero-byte files on the test side.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.