Restore back up strategy for history tables I have historical/log tables which are:


very large
holds data that is not rarely queried
only inserts are performed (constantly, very often)


I want to either compressed them or move them to cheaper(slow) storage.

Because of the tables structure (there are nvarchar/varchar max columns) I was not able to apply or achieved good results using row, page, column store or column store archive compressions.

So, I have decided to move them to cheaper storage. I guess I have to options here,  but let me know if I am wrong about something:


The tables are stored on slow storage. The database is under FULL
recovery model and transaction log backup is made each 15 minutes.
The issue is I am not able to perform partiatl backup (restoring
only important data from the fast storage) and apply transaction
log chain as well. Right?
To create mirror tables on the slow storage and continue to store
the history table on the slow storage. Each day a job is migrating data
from the primary history  tables to the mirror tables. So can I now
restore a partial backup and apply the transaction log chain as
well, as the tables on the slow drive are not same?
SQL Server supports piecemeal restores.  From that link:


  Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.


So you should be OK with the FULL recovery model.

I'd suggest you partition your tables.  The log tables especially should be amenable to partitioning by write date.  Direct the older partions to a filegroup on the slower, cheaper storage.  Then you can restore the important (i.e. recent) portions first and the ancient logs later.