Restrictions On A System Database

As many of you know, the [model] database is used as a template for newly created databases on SQL Server. I don't tend to make too many changes to [model]. I'll set file parameters for autogrowth. I may set the RECOVERY MODEL. I may even go so far as to add a USER, for example. That's about as far as it goes, unless there's a specific business need on a particular instance.

One thing I recently learned is that you can't add files to [model]. I was hoping to impact the number of files a database has when it is created. It's common for db's to be created, with just the standard mdf/ldf pair of files. Then the db is loaded with varying amounts of data. After a certain threshold, it makes more sense to have multiple data files, based on the environment. Sure, I could add files later and "balance" the data across the files. But it would be nice to *not* have to do this; some down time may be required and LOB data presents its own challenges *LOB data...cannot be moved without creating a new table and exporting the data into it.

My "a-ha moment" arrived when I tried adding a data file and got this error:

Msg 5013, Level 16, State 1, Line 1
The master and model databases cannot have files added to them. ALTER DATABASE was aborted.

This may not be a surprise to you, especially if you've read the MSDN documentation. Clearly, I had not.

FWIW, here's a few other notable things that aren't allowed on [model]:

Adding filegroups.

Changing the collation.

Changing the owner.

Dropping the [guest] user.