The master database stores instance-wide information such as logins, endpoints, linked server information, and the server configuration. This database also contains the definition of all the databases that are attached to the instance of SQL Server. Needless to say, if the master database is corrupt or not available, it is not possible for SQL Server to be available for users.

SQL Server uses the tempdb database as a work area for grouping and sorting options, cursors, and other operational tasks. As a best practice, consider moving tempdb to its own set of physical disks.

The model database is used as a template for new databases. Any objects created within this database, or database options that you set within it, will also exist on every new database that you later create.

The model database is required and cannot be deleted since it is used by tempdb every time the SQL Server service starts. To see how the model database works, create a table in the model database using the following code:

USE [model]
GO

CREATE TABLE Sales
(i INT)
GO

USE [master]
GO

CREATE DATABASE SmartSalesDB
GO

Now that you have created the new database, take a look at the tables within the SmartSalesDB database, as shown in the following code:

USE [SmartSalesDB]
GO

SELECT name,type_desc FROM sys.tables
GO

The results for this query are as follows:

name    type_desc
Sales    USER_TABLE

Here, the Sales table was created for you automatically since it was defined in the model database.

MSDB is used to store information for various components of SQL Server. Some uses include database backup and restore history, maintenance plans, and maintenance plan history as well as information about the SQL Server Agent job-scheduling service.

Depending on your implementation of SQL Server, MSDB might be your most heavily used system database outside of master. As with any user-defined database, any time you make changes, you need to back up this database. MSDB is no exception since actions such as changes to SQL Server Agent jobs, changes to policy management, or the creation of an Integration Services package will change MSDB.

Another important note is that there are special database roles defined in MSDB to support the various components. For example, the db_ssisopreator database role allows users in this role to execute all packages, but it cannot delete or change any packages. The SQLAgentReaderRole database role allows users in this role to view job execution information for all jobs but have full control only for the jobs the users own. Since there are so many components leveraging this database, take caution about adding users to the MSDB database.

You cannot by default see the resource database in SSMS. The resource database stores all system procedures, catalog views, functions, and other system-related objects in a read-only mode. Each instance of SQL Server has one mssqlsystemresource.mdf file and one mssqlsystemresource.ldf file located in the Binn folder of the SQL Server instance. You do not need to worry about backing up these files because the only way they change is when a hotfix or service pack is installed.

To set a database that is already started to single-user mode, you can issue the following statement:

ALTER DATABASE [SmartSalesDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Three options are available when you change to single-user mode. The ROLLBACK IMMEDIATE option means connections will be closed immediately, and noncommitted transactions will be rolled back. If you want to give a delay before you do this, you can specify ROLLBACK AFTER <X> SECONDS, where <X> is an integer value. If you want to place the database only if there are no open transactions, then use the NO_WAIT option.

If you want to have more than one connection to the database but need to limit the amount of users connected, you can put the database in a restricted state. This state is ideal for data imports and allows only members of the db_owner, dbcreator, and sysadmin roles to access the database. To set a database in restricted mode, you can issue the following query:

ALTER DATABASE [SmartSalesDB SET  RESTRICTED_USER
GO

Remember to change the database setting back to MULTI_USER once you are ready. You can do this with the following script:

ALTER DATABASE [SmartSalesDB SET  MULTI_USER
GO

Databases can be taken offline when you need to copy or move the database files. This is an extremely rare event since there are other tools available, such as the Copy Database Wizard in SSMS, to move data and/or databases to new servers. To set a database offline, use the following script:

ALTER DATABASE [SmartSalesDB SET OFFLINE
GO

In some rare occasions, it may be necessary to start the SQL Server instance in single-user mode. If you are trying to recover a damaged master database or any other system database, you will need to put SQL Server in single-user mode. You can do this by starting the service with the -m command-line argument. To place the SQL Server instance in single-user mode, perform the following steps:

To test the new server state, launch SSMS and make a connection to your server. If SSMS has the Object Explorer tree open, try to click the New Query button. This will cause an error dialog box to pop up and show text similar to the following:

When you set SQL Server in single-user mode, it accepts only one connection, and since Object Explorer in SQL Server is one connection, by opening a New Query you are requesting another connection to SQL Server. Thus, you receive the failure message. To use SSMS with SQL Server in single-user mode, make sure only Object Explorer or a Query Editor window is open, but not both. You can also use the command-line tool called SQLCMD to issue queries against this single-user server. You will learn more about SQLCMD in Chapter 10.

To remove the single-user restriction, go back to the Properties dialog box of the SQL Server service in SQL Server Configuration Manager and remove the -m parameter you added previously. Restart the SQL Server service, and it will allow multiple connections.