Why do system objects need to be restored prior to user databases?



  • Link: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/choose-a-database-engine-upgrade-method?view=sql-server-ver15#migrate-to-a-new-installation

    enter image description here

    I'm migrating sql server 2016 to 2019 (on prem).

    On the right side, why is the script in system objects step (step number 3 in pre-stage) before the user databases restore step?

    System objects includes grant/revoke/deny on stored procedures so why would that need to be done prior to restoring the databases?



  • I'm not sure what you mean by "System objects includes grant/revoke/deny on stored procedures". That is typically not what we mean by this. We mean stuff outside of the database, like logins, linked servers, sp_configure settings etc.

    Anyhow, that recommended order seem to be a mistake or at least a simplification made by the author of the article.

    Logins, for instance, is IMO better handled efter the restore. The reason is that the login might have that database as a default database. Creating it prior to the restore will fail unless you create it with some other database as default (tempdb, for instance) and after the restore change it to the restored database. I.e., it is easier to just restore first and then create the login.

    So, think about each type of "system object" (if we can use that term) and based on what it is, you can decide if it is better done before or after the restore.

    Here, btw, is my take on stuff to handle when moving database (which is what you are doing with a side-by-side upgrade): https://karaszi.com/moving-a-database-between-two-sql-server-instances




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2