0

Link: https://learn.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?

2 Answers 2

3

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

0

From the same link, it is mentioned as:

System objects: Some applications depend on information, entities, and/or objects that are outside of the scope of a single user database. Typically, an application has dependencies on the master and msdb databases, and also on the user database. Anything stored outside of a user database that is required for the correct functioning of that database must be made available on the destination server instance. For example, the logins for an application are stored as metadata in the master database, and they must be re-created on the destination server. If an application or database maintenance plan depends on SQL Server Agent jobs, whose metadata is stored in the msdb database, you must re-create those jobs on the destination server instance. Similarly, the metadata for a server-level trigger is stored in master.

There is another part in continuation of above:

When you move the database for an application to another server instance, you must re-create all the metadata of the dependent entities and objects in master and msdb on the destination server instance. For example, if a database application uses server-level triggers, just attaching or restoring the database on the new system is not enough. The database will not work as expected unless you manually re-create the metadata for those triggers in the master database

System object is not only to do with objects inside a user defined database rather many aspects which is beyond user database like master or msdb database and hence system objects are before the user database restoration step.

Similar steps have been suggested through script level here

Hope this helps.

5
  • The question is about the order.
    – variable
    Mar 10 at 9:44
  • Yes. Without having these objects already in the system databases, application may not function appropriately. Mar 10 at 10:06
  • But without the database already restored, the script that restores permissions like grants on stored procedures will fail, won't they? Because the db doesn't exist at that point.
    – variable
    Mar 10 at 10:26
  • 3
    You keep referring to granting permissions on stored procedures. There's no need for that. Those are inside the database already, i.e., restored with the database restore. Mar 10 at 10:54
  • Anything at user database will be part of database restore however system objects are there to look after any dependencies defined at system databases level like login, server level trigger etc. Mar 10 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.