Does the recovery model in Microsoft Sql Server matter when using Subset or Privacy?
Situation
When using Subset or Privacy on a Microsoft Sql Server database you might encounter performance problems or transaction logs filling up.
Might it help to do something with the recovery models Microsoft Sql Server supports?
Explanation
Yes.
Microsft Sql Server supports three types of recovery models
Thes models help you to save/restore data in case of crashes.
- Simple, no logging.
- Bulk_logged, minimal logging for bulk updates. No point in time recovery.
- Full, maximal logging, Point in time recovery.
See: https://msdn.microsoft.com/en-us/library/ms189275.aspx
Subset
Using Subset you will not create data but you copy data. That means that in most cases you can repeat your actions and never lose data.
For that reason it is recommende to use the Simple recovery model during subsetting.
After subsetting it is recommended to set recovery (back) to FULL.
When using append or unique append you will need backups before running Subset. This will enable restore the (target) database when it crashes during Subsetting.
Privacy
Using Privacy temporary tables are created en filled with data. Normally it is not required to keep the logging for these temporary tables.
For that reason it is recommende to use the Simple recovery model during anonimizing.
After anonimization it is recommended to set recovery (back) to FULL.
You need to have alter database permission to execute:
use master;
alter database <DBNAME> set recovery SIMPLE;
Related articles