How can I can I reduce the size of a SQL Server database after being restored and massive embedded files stripped?
My project has the following set up for the production and preproduction ("clone") environment.
- Production is not accessible at all for the development team, only the preproduction database.
- Preproduction database is restored each day, but a post-restore script is run to strip away sensitive data such as embedded files (VARBINARY(MAX)) and e-mail addresses
- This process leaves a preproduction database of about 50GB with 46GB available space which means that a lot of space is wasted
How can I optimize the used storage for the restored database?