The existence services will appear listed in the right panel. To do this, open SSCM, go to SQL Server Services in the left panel. But as opposed to the regular databases, to move master database file locations you will need to use the SQL Server Configuration Manager (SSCM). Moving the master database file locationsĪs any regular database, master data and log files can be moved to another location if needed. It is recommended to have regular backups of the master database since it will be very useful when the master database becomes unusable.

When any of these changes occurs, it is also recommended to perform a backup of the master database. A change in the master database can also be caused by changes in the SQL Server instance configuration or a SQL Server patch has been applied. Usually changes in the master database only occurs when there are changes in system objects like add/changing/deleting logins, endpoints or linked servers. The SELECT permission can be denied for any user as it is for a regular database or even, the public database role can be revoked to control which users may query the metadata from the master database. For example, in the case that master database being rebuilt, all user objects will be lost.īy default all users that have access to the SQL Server instance are granted to perform SELECT operations in the master database in the behalf of the public database role. The master database should stay as static as possible. If the master database does not exist or cannot be read then the SQL Server instance cannot start.Įven it is possible to create user objects in master database, it is not recommended to do so. The information that a SQL Server instance needs is stored in the master database, like the information about all existing databases and the location of their data and transaction log files. SQL Server uses the master database to record all information about the SQL Server instance system, like login accounts, endpoints, linked servers and configuration settings. In this article I will focus on the master database.

The first one was about the tempdb database. This is my second article about SQL Server system databases. There are at least 4 system databases in any SQL Server instance as shown by the following SQL Server Management Studio (SSMS) screen capture:

Sql server master database