I had to restore some production databases to a different drive on the same Microsoft SQL Server 2005 machine. That worked fine and the application using the databases is back online.

However, i have not restored the system (or default?) databases that SQL Server creates on its own (model, master, etc.).
My question is, what is the role of these databases? and how important it is that i restore them?

1 Answer
1

master and msdb store contain procedures, functions, tables, views and other objects that define the entire system. For example, master contains things like information about logins, where the files for the databases are located, system stored procedures, and lots of other stuff. msdb was originally mostly about jobs, alerts. dbmail configuration information and lots of other stuff has been added over the years.

model is sort of sample database that acts as a starting place when you create a new database. Objects that are in model will be copied to the new database. People used to leverage this behavior more often (like 10 or 15 years ago), so they could include a particular procedure or table in every database that they created, but this has it's problems and has fallen into disuse. BUT, kind of like an appendix, model is not terribly but you still need it.

tempdb is a database that provides temporary storage for temporary tables (#sometablename and ##someothertablename), table variables (if they are big enough), sorting operations and grouping operations (like GROUP BY or DISTINCT), and probably other, more arcane things. Normally, you would never restore a tempdb. It gets rebuilt from scratch every time you restart the server.

Recent versions of SQL Server have another, hidden system database called the "resource database". You should never have any reason to tinker with that.

In case you were wondering, "pubs", Adventurworks and AdventureworksDW are not system databases, they are just regular user databases that function as examples and they may be removed without affecting server behavior.

System databases are generally only restored when there has been some sort of catastrophic failure, like a disk crash or the data center burning down. You might also restore system databases if you are migrating from one server to another and need to preserve logins, jobs, dbmail and other configurations (though there are other, possibly better, ways to do that).

You want to back up master, model and msdb. You don't need to back up the resource database or tempdb. If one of your job functions is "dba", it is a good idea to review what you might have to do in order to restore these databases (it's documented in MSKB articles) because restoring a system database is more complicated than restoring a user database. You don't want to read those KB articles for the first time when you are actually in an emergency situation.

When restoring a user database, you don't need to restore the system databases. The catch with restoring a user database is that you need to make sure that the users described in the database match up with the logins for the server (which are stored in the master database). That is what the sp_change_users_login system procedure is for.