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?
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
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)?
16 People are following this question.
