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:

  1. 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?

  2. 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?

  • Did you mean "is only rarely queried?" – Michael Green Nov 25 '14 at 9:36

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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