I`ve got a 50 Gb database, in which a table takes up 43 GB, and it's full of bad data(over 90%), and i plan to clean it. Database is in Full recovery mode.
My plan is: 0 - take a backup for safety reasons 1 - put database in simple recovery mode 2 - remove 90% of bad data from that table (use batches so that log file doesn't grow) 3 - shrink the data file to an acceptable size, so estimated data for next 1-2 years wont cause any file growth (ofc i will need to rebuild all indexes) 3 - put database back Full recovery mode 4 - take a full backup of the database, so the log backup chain is restored from this point on
Now, my question is a bit strange, but, am I doing anything wrong ? especially by switching database from full -> simple, then simple -> full ? Am I missing anything important from this plan ? Is this the best approach to my problem ?
I hope i gave all needed details, and my questions are not too vague. Thanks.
Your plan is fine. There is no risk in switching between recovery modes in your scenario. Your DB will be in Full mode after last backup.
Only one note, if you need only 10% of data, wouldn't be faster to create new database (properly sized), copy over valid data, rename or drop old DB, rename new DB to replace old one. Answer depends on DB complexity, environment workload etc. But it can save you a lot of time on deletes in batches, rebuild tables to get rid of fragmentation and DB shrink. So it is worth to consider.