Attribute to identify system databases

Question

Post reply

Attribute to identify system databases

Robert Frasca

SSCertifiable

Points: 6026
More actions
March 12, 2010 at 8:09 am

#219861

I'm embarrassed to say that I can't seem to figure this out. Is there an attribute along the lines of database_type that I can use to differentiate "system" databases from "user" databases. Clearly there is, as the maintenance plan wizard seems to know which category a database falls into, but I can't find that attribute.
"Beliefs" get in the way of learning.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

I don't believe there is one, but we know what they are..

master

model

msdb

tempdb

CEWII

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 2

I'm trying to generate some canned Maintenance Plans and I want to logically filter out the System db's or, as the case may be, filter out the user databases. The maintenance tasks available in SSIS can clearly do this but I don't want to have to manually configure each maintenance plan after I install it. (These are going to be installed on thousands of sites by non-SQL Server types.) I'd really rather not hard-code the names. Seems kind of bogus.

"Beliefs" get in the way of learning.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

I think this might be taking an anti-hard coding stance to far. Trust me, I'm generally against hardc-coding but since the system databases are the same everywhere and in every version I can think of I don't think it is warranted. And I've looked at the three places I could think of that might have such a flag and nothing. I'd hard code it at this point.

CEWII

Lowell SSC Guru Points: 323504 More actions · Answer 4

I'm with Elliot; you can assume that the dbID < 4, but, on my SQL 2008, I have 6 db's i'd call system db's:

master

tempdb

model

msdb

ReportServer

ReportServerTempDB

I think you are limited to using a list of known db names.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

Lowell (3/12/2010)
I'm with Elliot; you can assume that the dbID < 4, but, on my SQL 2008, I have 6 db's i'd call system db's:
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
I think you are limited to using a list of known db names.

I would tend not to classify those two as system databases, all the others are necessary to make the system work.

I also know that those names are also dependent on whether the reporting server is tied to a named instance. On my server they are:

ReportServer$SQLEXPRESS

ReportServer$SQLEXPRESSTempDB

CEWII

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 6

The fact that the names can vary based on whether it is a named instance is one of the reasons why I don't want to hard code. Of course, I may have no choice. It's entirely possible that not creating database "types" is by design since identifying (or not identifying) certain databases as "system" database may be considered an implementation choice. The only serious attempt that Microsoft makes to protect a "system" database is the Resource database. That is clearly by design.

With the idea that categorizing certain databases as "system" databases is an implementation choice, I will create a user table containing those databases that I decide are "system" databases and use that as one of my data sources in my maintenance package(s).

Thanks for the dialog, it was helpful in clarifying the direction I'll take.

"Beliefs" get in the way of learning.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 7

I guess I'm unsure what you are getting at..

master, model, tempdb, and msdb are ALWAYS named the same regardless of named or default instance. Everything else is in question..

CEWII

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 8

There's no guarantee that those names will stay the same. Also, from my perspective, the Reporting databases are system databases as well. Since the names of the four default system databases are unlikely to change, hard-coding them into a user table works fine and gives me the flexibility to add other databases like the reporting databases, the performance stats database (in SQL Server 2008) or the various databases dedicated to products like Sharepoint or third party products that aren't my concern. I can actually create my own categories and drive my maintenance plans using those.

Should TempDB suddenly be renamed WorkDB in the next release, all I have to do is change a table entry and not a bunch of code. By the same token, if a new system database appears in the next release all I need to do is add a row. Since I still have to hard-code those first four table names it's still not the most elegant solution but this way provides a lot more flexibility than hard-coding the names in the SSIS packages. I'd much rather write a script to update or insert a row versus the hassle of deploying multiple SSIS packages across thousands of servers.

"Beliefs" get in the way of learning.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 9

Elliott Whitlow

SSC Guru

Points: 102296

March 12, 2010 at 11:36 am

#1132896

ok..

CEWII

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 10

You have a good point, but I think you're coding around a very unlikely possibility. There is literally thousands of scripts, including system scripts in SQL Server that contain

IN ('master', 'msdb', 'model', 'tempdb')

I can't imagine this changing, and if it did, wouldn't it be a search and replace item in your code? Isn't that way, way easier than trying to code around something?

On top of that, however, does the type of db matter? If you are coding maintenance, than isn't the recovery model, and risk level of the db important? If you run backups of system dbs in Simple along with some user db in simple, does that cause you an issue?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Steve Jones - Editor (3/12/2010)
You have a good point, but I think you're coding around a very unlikely possibility. There is literally thousands of scripts, including system scripts in SQL Server that contain
IN ('master', 'msdb', 'model', 'tempdb')
I can't imagine this changing, and if it did, wouldn't it be a search and replace item in your code? Isn't that way, way easier than trying to code around something?
On top of that, however, does the type of db matter? If you are coding maintenance, than isn't the recovery model, and risk level of the db important? If you run backups of system dbs in Simple along with some user db in simple, does that cause you an issue?

Steve makes a good case here.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 12

We install our products on servers of clients who have little or no technical expertise in-house. It's in our best interest to make sure that some fundamental safeguards are in place on each server. We're also not always certain who else and what else we're sharing a server with. Originally, our products were installed on SQL Server 2000. Many of these clients have migrated to SQL Server 2005 but are running their 2000 maintenance plans using the DTS legacy component. This isn't an optimal solution and some of our clients are getting bit. We had one client whose database has been corrupt since, as near as we can tell, last October. The only reason they're still up is because the corrupt areas haven't been referenced. It doesn't matter that we don't have the responsibility of maintaining their databases, we're still going to get blamed. This is why we're embarking on creating new sets of portable maintenance plans that can be deployed to our clients.

I am philosophically opposed to hard-coding things even if Microsoft isn't. Throughout my career the decision to hard-code something has bit me more times than I care to count. Yes, it isn't likely that the names of those four databases will change and I've acknowledged that. Of course, I've also read about a million times on this and other forums that there is no guarantee that "X" will still work the same way in the next version so to hear encouragement to hard-code seems a little hypocritical. Additionally, the possibility of a new "system" database appearing isn't completely absurd even if the names of the others don't change.

I don't view my approach as coding "around" something. Instead, I view it as adding flexibility and ease of maintenance even if it takes me a few hours longer. Frankly, I'm a little surprised to hear people questioning the decision to use a table-driven approach rather than a hard-coded approach. I've spent years encouraging developers to use the relational engine rather than hard-coding to avoid it.

"Beliefs" get in the way of learning.

Paul White SSC Guru Points: 150467 More actions · Answer 13

The fact of the matter is that there is no perfectly sound way to identify 'system' databases.

The best you can do is to take a combination of the advice so far and put the logic somewhere that makes it easy to maintain.

You have to be realistic too. Yes, master could theoretically be renamed in a future version of SQL Server, but it is also true to say that we would all likely face larger problems that those presented in this thread.

For what it is worth, this is the code I sometimes use:

SELECT name = QUOTENAME(D.name)

FROM sys.databases D

WHERE (

D.database_id BETWEEN 1 AND 4 -- master, tempdb, model, and msdb

OR D.name LIKE 'ReportServer$%' -- Report Server

OR D.is_distributor = 1 -- Replication

)

AND D.source_database_id IS NULL -- not a snapshot

AND D.state_desc = N'ONLINE' -- is online

AND D.user_access_desc = N'MULTI_USER' -- open for all users

AND D.is_read_only = 0; -- not read-only

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 14

{Edit} Didn't see Paul's post... Paul has it right.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)