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.