We have a process of archiving wherein we take the backup of current database and restore it as xxx archive database. Now this database contains previous data and will not have any insert/update/Delete.
As this database will only be used for reporting, we are thinking of shrinking the database. However, this increases the index fragmentation. We are thinking of following below steps:
- Shrink the database with re-organize
- Re-organize indexes
Will this hamper the database performance? What other options can we consider for recovering the space?
Update: In the process, We change the recovery model to simple, backup the database to move the database out of Pseudo-Simple state, and then planning to shrink the database.