Shrinking database which is not having any insert/update

Question

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:

1. Shrink the database with re-organize
2. 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.

Answer 1

Instead of doing a backup/restore, consider moving data across into a new, presized database, and creating new indexes on this data. This should keep your file size small and your indexes unfragmented. Also make sure you have compression turned on.

Answer 2

As this database will only be used for reporting, we are thinking of shrinking the database.

A one-time sensible shrink is OK. Disk space is cheap !

Increases the index fragmentation is the side effect of doing a shrink operation. It's advisable to shrink using DBCC SHRINKFILE during maintenance window or when there is minimal activity. Shrink database in chunks using the script.

What other options can we consider for recovering the space?

Another option would be to script out the database and then bcp out and bcp in the data.

If you take the backup / restore route, make sure you enable backup compression (source server) and Instant file initialization (source and destination server). This will help cut down the restore time.

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.

I am not sure how this is related to shrinking the database. After doing a shrink and reorg, you can take a backup.

Since you are ONLY going to use the database for reporting, why not keep the database in read-only mode?

Answer 3

I think you will find that if you use an SSIS package to move your data between multiple servers @RobFarley's suggestion will work just fine. You are transmitting information across the network either way. In one case a backup file and in the other the data. However if you are absolutely against the idea you can use a combination of the two ideas.

1. Restore your backup to the new location.
2. Create a new database.
3. Move your existing data into it.
   • Compression is a good idea. You will probably find that the CPU cost isn't going to be that high and you will see a performance increase as well as a space decrease.
   • You can also use poor mans compression and set your FILLFACTOR to 100%.
4. Drop your restored database.

I wouldn't try using your initial method.

1. Shrink the database with re-organize
2. Re-organize indexes

By shrinking the database you shred the indexes (which is why you want to re-build them afterwards). Note I said rebuild not reorganize. That's because reorganizing only defragments the leaf level of the index. Unfortunately when you rebuild it requires extra space in the database. That means that your database is going to grow again (by at least the size of your largest table).

One last option.

1. Restore your backup to the new location
2. Create a new filegroup with enough space to hold all of your data (or possibly a bit less & let it grow a bit to be sure you get the minimum space).
3. Re-create your indexes (including the clustered ones) onto the new filegroup using whatever form of compression you choose. This removes data from the PRIMARY filegroup and puts it into your new filegroup.
4. Once all of your user data is in the new filegroup you can safely shrink the PRIMARY filegroup.