Sql-server – Backup and restore SQL server database with FILESTREAM filegroup

backupfilestreamrestoresql server

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.

Best Answer

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.

Related Question