Does the recovery model in Microsoft Sql Server matter when using Subset or Privacy?
Microsoft SQL Server supports three types of recovery models.
These 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
Before we continue, it is important to know that using Full logging is substantially slower than executing queries on a SQL Server database without any logging. The reason for this is that every time a transaction is made, a transaction log-line has to be made as well. Within a normal production database this can be desirable, as it enables you to recovery data in the event of data corruption or other events that cause data loss or damaged data. However, when making large transactions (such as copying over partial databases to a test database) it's feasible for the transaction log to fill up, and prevent any further transactions.
Subset
Subset does not create new data, but rather copy existing data. Because of this, it's not possible to definitively lose data, unless you append data onto an existing target database. If a Subset run fails, you can retry without the need to reset data.
Therefore, setting the recovery model to Simple is the fastest, without any of the associated downsides of not being to restore data.
After subsetting it is recommended to set recovery (back) to Full
When using append or unique append, it is recommended to backup the target database if you are unable to easily reset the data you are appending to. Then, if there is a crash, you can easily reset.
Privacy
During a Privacy run temporary tables are created and filled, and the target database (which contains test data) is modified at the end of a run. Normally it is not required to keep the logging for these temporary tables, or the target database.
For that reason it is recommended to use the Simple recovery model during anonymizing.
After anonymization it is recommended to set recovery (back) to Full.
You need to have alter database permission to execute:
Changing the recovery model
use master;
alter database <DBNAME> set recovery SIMPLE;