0

I have used the following query in my local computer to rebuild an INDEX on a table of database. After that I have created a .bak file to move the database to the server. I am not sure if rebuilding an INDEX is reflected into the .bak file or it is just a defragmentation process on the local HARD DISK?

USE MYDB;
GO
ALTER INDEX PK_Titles ON NEWS
REBUILD;
GO

Should I repeat this query on the server again?

Ali Sheikhpour
  • 10,475
  • 5
  • 41
  • 82
  • No it's not a defrag on the disk. It's an internal database operation. – Nick.Mc Jun 14 '18 at 22:13
  • Is there any reference or a test method to confirm that? @Nick.McDermaid – Ali Sheikhpour Jun 14 '18 at 22:16
  • I don't have a reference. If you wanted to test you could look at fragmentation of the MDF file before and after (you would find after that it might be even more fragmented). But with SSD, file fragmenation is not even a thing anymore. To test you could also check the index fragmentation at various points (before defrag, after defrag, after restore) with this script https://myadventuresincoding.wordpress.com/2013/05/27/sql-server-check-index-fragmentation-on-all-indexes-in-a-database . – Nick.Mc Jun 15 '18 at 01:35
  • 1
    Here a is a very old SO explaining that backup/restore preserves whatever you have at page level (including fragementation) https://serverfault.com/questions/33432/does-restoring-a-sql-database-from-backup-rebuild-its-indexes If your index is spread across 100 pages at backup time, it is exactly the same when you restore it. If you defrag that index down to 80 pages, it will be 80 pages in the backup and the restore – Nick.Mc Jun 15 '18 at 01:36
  • Lastly, here's an article on internal vs external fragmentation. http://www.dotnetfunda.com/articles/show/3535/fragmentation-in-sql-server-internal-and-external-fragmentation – Nick.Mc Jun 15 '18 at 02:22

0 Answers0