SQL Server System Databases are very simple and pretty small (msdb might be huge in some cases if we are not properly maintaining it) databases at a very high level, but the most(i mean it) important databases within SQL Server. Why am i stressing the word “the most Important”??? Because SQL Server Stores it majority of the configuration information in these Databases besides storing some in Registry and few configuration(ini/xml) files and SQL needs them in a healthy shape to be in running state. So what are the System Databases in SQL Server?
Master, MODEL, MSDB, TEMPDB, RESOURCE Database and Distribution(I would call it as a pseudo System Database 😉 You will see this DB only if you’ve configured replication). Let me explain what each database does in brief…Let’s start with Master Database.
Master: The Very first Database loaded by SQL Server on Service Start. If if finds any issue bringing up this database, entire SQL Server Instance will be down. Master Database stores very critical information regarding Instance configurations such as CPU info, Memory configurations, other sp_configure settings etc. It stores the Information(Meta Data) of all other databases on the respective instance. Stores Logins information and all other Instance scoped objects(Server Objects) such as Linked Servers, ENDPOINTS etc…
Model: This little guy acts as a TEMPLATE Database. whatever settings you configure on this database, they will be reflected on any other new database you create on this Instance(Classic Example – Setting Model Database Recovery Model to SIMPLE on your DEV Server, making sure that all the databases which you are going to create from now on will be in SIMPLE Rec mode on this Intance). If you create any object(let’s say a user table) inside Model database, all the new databases which you create from now on contains the same object! So…..Basically you can use this guy for your standardization!
MSDB: Well, this guy stores all the critical information any thing related to Automation. Soo….anything which you do with SQL Agent, it gets stored or it updates MSDB Database in one or the other form. All your Agent Jobs, Maintenance Plans information, Backup/Restore Events/Alerts/DB Mail information, even SSIS packages(if you are doing MSDB Deployments) and So on…..! Very Very Important and heavily used by Agent Service.
TEMPDB: The busiestttttttttttttt System Database out of all! Ignoring TempDB configuration will heavily impact your SQL Server performance. As the name implies…it’s a temporary Database where all the temporary objects will be created, Cursors use TempDB, Row Versioning is done in TEMPDB(SNAPSHOT ISOLATION), ONLINE INDEX REBUILDS(choosing Store/Sort Results in TEMPDB), Memory Spills occur a lot to TEMPDB with Hash Joins and Hash Recursions and even SORTS and many more things heavily uses TEMPDB. Interesting thing to note regarding this Database is, It’ll be “Wiped Out” entirely when we shutdown SQL Server and Brand new TEMPDB will be created at Start up of SQL Server. So….there’s no concept of “CRASH RECOVERY ON TEMPDB” in SQL Server/You can’t take a Backup of TEMPDB.
RESOURCE DATABASE: This guy will always be in “STEALTH” Mode hiding from us. We can not see this database from our SSMS. We’ve to actually go to our File System to look at the Data and log files of this Database. It is a Read-Only Database containing “System Objects” of SQL Server. This guy is very very important while we are upgrading our SQL(applying Hotfixes/Service Packs/ CU’s etc)which many of the DBA’s ignore to take backup before applying Patches!!!
I believe that’s enough information to understand how critical these Databases are! For more information on these Databases check BOL….MSFT has tons and tons of very useful information on these Databases.
Backups on System Databases:
Well, now let’s see how can we secure these Databases. In other Words how to design a Disaster Recovery strategy on these System Databases. I’ve seen few(TBH Many) cases where a DBA ignores Backing up the System Databases….Very Very Bad!! Trust me guys…you’ll pay the penalty someday or the other if you ignore backing up these databases!
Before proceeding any further, let me tell you something…You Should never Ignore backing up and performing frequent Consistency Checks on your System Databases. In a worst case scenario where you have to rebuild your entire server, you would need them to be restored without any issues. ( Imagine a case where you have 100+ Jobs and 300+ Logins and 20-30 Linked Servers and you don’t have your System Database Backups….It’s scary enough just to imagine right?? ) FYI I’ve shown how to Restore Master and MSDB Database here.
Note: You’ve to know your SQL Server Instance Collation Information to rebuild your SQL Server(Rebuilding Master DB). I’m saying…rebuilding master, not Restoring your master database in this collation context. I’ll show you how to rebuild entire SQL Server in later posts.
Master Database – Take Full backup as often as you do for your User Databases. You can’t take Log backup for Master Database. Only Full backups are allowed on Master Database.
Model Database – Well, it’s your call! if you are using it, you can take backups..if not you can ignore. I personally feel taking Model backups is just a safe bet and the size of the Model Database backup will be usually in few Kilo Bytes, which is almost negligible!
MSDB Database – You’ve to take regular Backups. You can perform all types of Backups on MSDB(Full/Diff/Log). Plan accordingly!
TEMPDB – You can’t Backup TEMPDB Database. As i mentioned above, SQL will recreate tempdb on every service restart.
Resource Database – This should be our part of Backup Strategy(whenever possible). At least once a week or bi-weekly! You should not ignore backing up this especially when you are patching/upgrading your SQL Server. So..where should we locate this hidden Database?? The Answer is “in your SQL Server Program Files(location where you installed your SQL Server Binaries) starting SQL Server 2008, not in the Data file(s) Directory anymore”. You can see in the Below Screenshot for the default location of this Database files on one of my Instance
So, in my case the path is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Binn”.
For backing up this Database, all we need to do is, Just copy these two files and place them in your secure backups location. So…We can’t do a SQL Backup on this Database, the trick is to copy the files at File System Level.
That’s pretty much it Guys!! Hope this is useful and informational…
MSSQLTREK 