Tables, stored procedures, views, logins, constraints, etc, etc. It’s all just data. In every database system I’ve ever worked on there are system tables underneath everything that contain 99% of all of the metadata. (Metadata, in this case, is the data describing the databases and their contents as well as system information.)

In fact, in some older systems, you could even edit that data. Can you imagine being able to change the width of a column buy updating a row in a system table? It was never a good idea (oh the fun ways we broke things) and these days I don’t see that ability anymore. For that matter, in SQL Server, you can’t even see the system tables except under very very specific circumstances.

You might be asking “Why does this matter?”

Quick example. Server level principals (logins) are stored in the master database. Database level principals (users) are stored in each individual database (including master). If I take a database and restore it to a new instance can the database level permissions change? Only if I deliberately change them. They are part of the database so are restored with the database. Will the server level principals be there? Only if I create them.

If you think about where the metadata is stored, then you will have fewer questions when things move around.

Another example. Jobs are stored in msdb, along with job permissions, job history etc. If I create a new instance and restore all of my user databases to it, but none of the system databases, will I have my agent jobs? Of course not. Will my users have their job permissions? Nope, not those either.

There are lots of examples that trip people up. Particularly when moving between instances. Restores, AGs, replication and the like. Next time you are confused about why something didn’t move from one place to another think about where it’s stored and what you actually moved.