How to reorganize MS SQL Server database
Edit 1: Aaron noted in a comment that compression can be beneficial here. Good point. Compression options include columnstore indexes (clustered or nonclustered - let's not go into details at this point), row compression and page compression.
As you know, backup and restore will give you a binary copy of the database, so it won't help you.
Shrink will fragment the indexes even more since it move pages towards the beginning of the file without consideration of the page ordering of an index.
So you are left with index reorg or rebuild. Rebuild is more thorough since it creates a new index. But it need space for this. So if you shrink and then rebuild, then the rebuild will create a new index &quot;at the end of the file&quot; possible making the file grow (depending on how much free space you left when you did the shrink).
So, it is a bit of a contradiction to both have stuff nicely aligned and have no free space. What you can hope for is to have free space corresponding to you your largest allocation (index/heap) in the file. I.e., &quot;working space&quot; for the largest one.
(If that isn't acceptable, then you are left with deciding which allocations are too large to have spare room for (remember, you only need spare room for one). For indexes you can drop them, shrink and then create. For the data (clustered index and heap) you'd have to export outside SQL Server, truncate the table, shrink and then import the data.
Edit 2: Nikita added a good point on moving the data to a different filegroup as an option to exporting it outside SQL Server. Good point and definitely worth considering. If you decide to go the &quot;get the data out of the filegroup&quot; route.)
How to go about this? Use Ola's scripts as suggested by Nikita for your B-trees. And for your heaps (I hope you don't have that many) you can for instance use my stored procedure.
So, shrink first, then rebuild your b-trees and heaps. And it can take a very long time, especially the shrink (depends on if you have LOBs, heaps etc).
Come to think about it, we have two options for exporting and importing the data:
BACPAC. This will give you a zipped file containing an XML file describing your tables and BCP files with the data. And then you use a tool to import based on this. You can use BACPACs from SSMS (right-click a database and the databases folder), sqlpackage.exe and of course there are a few Powershell options. BACKPACs are designed to work with Azure SQL database so it will choke on anything what isn't available there, AFAIK.
SSMS Generate Script. You find this if you right-click a database. This will create a .sql file containing DDL and INSERT. Cross your fingers that it does it right. And for 500 GB..., I have my doubts. Anyhow, you can also access this functionality using SSIS, if that is beneficial for you.