question

gotqn avatar image
gotqn asked

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: 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?
restoresql-server-2014backup-restorestorage
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you partition the table and put the cold (old data that doesn't change) in its own file group. Then perform file group backups less frequently than your hot data.
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
You can do a piecemeal restore, but only if you have Enterprise edition. But, your first question is wrong. It's best that you're in FULL recovery mode and have the transaction log available. You need to make sure that the history table is put into it's own filegroup within the database. Microsoft has the whole process [pretty well documented][1] with a number of examples. I'm not sure I understand question 2. Are you saying that some of the data won't be in the history table and if you restore that there could be data loss? But that's only if you don't also restore the rest of the database. If you look at how piecemeal restores are done, you still have to restore the main part of the database too. So everything should be fine there, assuming I understand what you're asking. [1]: http://msdn.microsoft.com/en-us/library/ms177425.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks this sounds writes. I guess there will not be any issues to have a READ-ONLY file group, and to set it to be READ-WRITE when I need to add more data ( http://msdn.microsoft.com/en-us/library/bb522469.aspx)?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.