-3

I have created a new server, restored the system dbs and presented a copy of the data/log disks from the previous machine (or alternatively maybe I restore the user dbs using the .bak files).

Post SQL Server migration, why do the following need to be performed:

  1. Index rebuild on all databases
  2. Statistics update on all databases
  3. Repopulation of all full text indexes (or rebuild all full text catalogs)

Example: https://www.sqlservercentral.com/articles/sql-server-migration-1

Does the answer differ whether the upgrade was done in-place vs by restoring from bak files? And server version vs different version?

variable
  • 3,590
  • 4
  • 37
  • 100

1 Answers1

4

I'm going to address 1 and 2:

Who says that you should rebuild indexes and update statistics? There is nothing inherent to a migration regarding these two operations.

Statistics is included in the database, and there are no format changes between versions (there was in one version change a long long time ago, but that is ancient).

Indexes will have the same fragmentation level after the migration as they had before the migration.

Above assumes that you do in-place, backup/restore or attach/detach migration. Other methods are different since they don't give you a binary copy of the database.

(I know that a frequent recommendation is to update statistics. That is IMO bad recommendation, since people then to believe that you "have" to do it. If you have a 5 TB database and believe that you "have" to update statistics, then that will limit your options for migration (due to the time it takes to update statistics for a large database).)

Addition and hopefully clarification:

A method that gives you a binary copy of the database doesn't require update of stats or rebuild of indexes. Such methods include presenting a disk with the database files (as in your case), backup/restore and detach attach.

Other methods that don't give you a binary copy of the database includes for instance exporting the database to a BACPAC file. We are now in the export/import territory, and the quality of your statistics and external/internal fragmentation of your indexes depends on how things are done by this particular tool so it is impossible to generalize and answer for such tools.

To summarize: Don't believe what you read. You might read some article that say that you should/need do any of these things. You your own understanding and experience instead of just going to what the author of that article say.

Tibor Karaszi
  • 18,269
  • 2
  • 17
  • 30