Welcome back to another blog post in our series on statistics for Read-Only Databases. In our previous articles, we discussed the location of statistics before and after a database is marked as read-only, as well as how to script statistics from SSMS. Today, we will address a common question that many of you have been asking: how to move statistics from temporary status to permanent status in a read-only database.

Before we dive into the solution, it’s important to note that moving statistics from the TempDB to a read-only database should only be done if you are confident that the same statistics are created multiple times and there are no changes in the queries running against the read-only database. If your read-only database is refreshed daily, weekly, or at regular intervals, it is not recommended to move the statistics until the database is in a consistent stage where data is not changing frequently.

Now, let’s get to the solution. The first hurdle we encounter is that we cannot create statistics in a read-only database. A database must be in read-write mode to create statistics. Therefore, to move the statistics from TempDB to the read-only database, we need to change the status of the database from read-only to read-write temporarily.

Here are the steps to move the statistics:

1. Change the database status to read-write:

  
  USE [master]
  GO
  ALTER DATABASE [dbname] SET READ_WRITE WITH NO_WAIT
  GO
  
  

2. Follow the steps in our previous blog post on how to script statistics from SSMS to generate the script for the statistics in the read-only database.
3. Execute the script generated from step 2 to create the statistics in the read-only database.
4. Change the database status back to read-only:

  
  USE [master]
  GO
  ALTER DATABASE [dbname] SET READ_ONLY WITH NO_WAIT
  GO
  
  

Once the statistics are generated based on your read-only database, they will have an additional suffix “_scripted” at the end of their names. For example, if your original statistic’s name is “MyStatisticsName_readonly_database_statistic”, it will be automatically converted to “MyStatisticsName_readonly_database_statistic_scripted”.

We hope this blog post has provided you with a clear solution to the question of moving statistics from temporary to permanent status in a read-only database. If you have any further questions or feedback, please let us know. Stay tuned for more articles in this series!