SQL Server System Databases and Recovery Model

SQL Server System Databases and Recovery Model

1) What are system databases?

When we install Microsoft SQL Server, five system databases are automatically created for each SQL Server instance. These system databases allow the database engine and administrative applications to properly manage the system:

• master
• model
• msdb
• tempdb
• Resource (SQL Server 2005 and higher only)

2) What is master database?

The Master database is the heart and soul of SQL Server. It basically records all the system level information. Every instance of SQL Server will have an independent Master database; as it captures instance level configuration information. The information which is captured in the Master database includes SQL Server instance level configurations, linked server configurations, SQL Server Logins, Service Broker Endpoints, System Level Stored Procedures, and System level Functions etc. The system and user databases related information such as name and location for user and system database are captured in Master database.

If master database is corrupted or if it is not available then the SQL Server Service will not start. In SQL Server 2005 and later versions the system objects are stored in Resource Database rather than in Master Database. The Master database is created using Simple Recovery Model.

3) What is model database?

The Model database is basically used as a template when creating databases in SQL Server. Basically SQL Server takes a copy of Model database whenever a user tries to create a new database in SQL Server. This also means that if a user creates any tables, stored procedures, user defined data types or user defined functions within a Model database; then those objects will be available in every newly created database on that particular instance of SQL Server.

If the Model database is damaged or corrupted then SQL Server Service will not start up as it will not be able to create the tempdb database.

4) What is msdb database?

SQL Server Agent uses MSDB database to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators etc. MSDB also stores information related to configuration of Service Broker, Log Shipping, database backups and restore information, Maintenance Plan Configuration, Configuration of Database Mail, Policy Bases Information of SQL Server 2008 etc.

If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost. This will result in the failure of all scheduled activities.

5) What is tempdb database?

The tempdb database is considered a global resource that all connections and all users can access. The tempdb database holds user-created temporary database objects, such as temporary tables and temporary stored procedures. This database is also used heavily by the database engine to serialize intermediate results and to hold other temporary data and objects that are generated during processing. Tempdb is always recreated from when the SQL Server service is first started. Because tempdb is so heavily used by the system, many system-wide performance optimizations are necessarily focused on making tempdb as efficient as possible.

6) What is resource database?

The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database. The Resource Database does not contain any user data or any user metadata. By design, the Resource database is not visible under SQL Server Management Studio’s Object Explorer | Databases | System Databases Node.

The DBA shouldn’t rename or move the Resource Database file. If the files are renamed or moved from their respective locations then SQL Server will not start. The other important thing to be considered is not to put the Resource Database files in a compressed or encrypted NTFS file system folders as it will hinder the performance and will also possibly prevent upgrades.

7) What is the location of system databases?

We can specify the location of system database under Data Root Directory option.

8) What is the location of resource database?

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

9) What are the typical objects that are created in the TempDB database?

There are three different types of objects stored in tempdb.

• Internal Objects:

1. Intermediate runs for sort.
2. Intermediate results for hash join and hash aggregates.
3. XML variables or other large object (LOB) data type variables. (text, image, ntext, varchar(max), varbinary(max))
4. Queries that need a spool to store intermediate results.
5. Keyset cursors to store the keys.
6. Static cursors to store a query result.
7. Service Broker to store messages in transit.
8. INSTEAD OF triggers to store data for internal processing.
9. DBCC CHECK internally uses a query that may need to spool intermediate results.
10. Query notification and event notification use Service Broker.

• Version Store:

1. Snapshot Isolation / Read Committed Snapshot Islotaion
2. Triggers (After Triggers). Instead of triggers doesn’t generate versions.
3. MARS (Multiple Active Result Sets)
4. Index Rebuilds

• User Objects:

1. User defined tables and indexes
2. Local and global temporary tables, bulk insert and BCP intermediate results
3. Index rebuilds with “SORT IN TEMPDB” option.

10) What is the database that has the backup and restores system tables?  What are the backup and restore system tables?  What do each of the tables do?

The MSDB database is the database with the backup and restores system tables. Here are the backup and restore system tables and their purpose:

backupfile – contains one row for each data file or log file backed up

backupmediafamily – contains one row for each media family

backupmediaset – contains one row for each backup media set

backupset – contains one row for each backup set

restorefile – contains one row for each restored file

restorefilegroup – contains one row for each restored filegroup

restorehistory – contains one row for each restore operation

11) How to check the version of Resource database?

SELECT SERVERPROPERTY('ResourceVersion');

GO

12) How to determine when the Resourcedatabase was last updated?

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

13) What are the system databases created when we configure Reporting services?

ReportServer and ReportServerTempdb

14) How to move model database?

a. Determine the logical file names of the msdb and model database and their current physical location on the disk.

USE master

Go

SELECT DB_NAME(database_id)  AS "Database Name", name AS "Logical File Name",

physical_name AS "Physical File Location",

state_desc AS "State"

FROM sys.master_files WHERE database_id IN (DB_ID(N'model'))

Go

b. For each file to be moved, run the following statement by providing the logical file name and the new physical location of where the file will be moved to.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

c. Stop the instance of SQL Server.

d. Move the physical files to the new location.

e. Start the SQL Server instance.

f. Verify the location using the script used in step a.

15) How to move msdb database?

a. Determine the logical file names of the msdb and model database and their current physical location on the disk.

USE master

Go

SELECT DB_NAME(database_id)  AS "Database Name", name AS "Logical File Name",

physical_name AS "Physical File Location",

state_desc AS "State"

FROM sys.master_files WHERE database_id IN (DB_ID(N'msdb'))

Go

b. For each file to be moved, run the following statement by providing the logical file name and the new physical location of where the file will be moved to.

ALTER DATABASE msdb MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

c. Stop the instance of SQL Server.

d. Move the physical files to the new location.

e. Start the SQL Server instance.

f. Verify the location using the script used in step a.

g. Verify that Service Broker is enabled for the msdb database by running the following query.

USE master

Go

SELECT  is_broker_enabled FROM    sys.databases

WHERE   database_id = DB_ID(N'msdb');

Go

If is_broker_enabled is not 1 then enable the service broker.

ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Go

16) How to move tempdb database?

• a. Determine the logical file names of the msdb and model database and their current physical location on the disk.

·         USE master

·         Go

·         SELECT DB_NAME(database_id)  AS "Database Name", name AS "Logical File Name",

·         physical_name AS "Physical File Location",

·         state_desc AS "State"

·         FROM sys.master_files WHERE database_id IN (DB_ID(N'msdb'))

Go

b. For each file to be moved, run the following statement by providing the logical file name and the new physical location of where the file will be moved to.

ALTER DATABASE tempdb MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

c. Restart the instance of SQL Server.

d. f. Verify the location using the script used in step a.

e. delete the old Tempdb database files from the old location.

17) How to move master database?

1. Open SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server for example,  and choose Properties.
3. Open Startup Parameters dialog.

4. Edit the Startup Parameters values to point to the planned location for the master database data and log files,     and click OK. Moving the error log file is optional.

• In theSQL Server (instance_name) Properties dialog box, click the Startup Parameters
• The parameter value for the data file must follow the-d parameter and the value for the log file must follow the -l  The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf -eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\ERRORLOG -lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

• If the planned relocation for the master data and log file isF:\MSSQL\Data, the parameter values would be changed as follows:

-dF:\MSSQL\Data\master.mdf -eC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\ERRORLOG -lF:\MSSQL\Data\mastlog.ldf

5. Stop the instance of SQL Server.

C:\> net stop SQLSERVERAGENT[SQLPROD1] C:\> net stop MSSQLSERVER[SQLPROD1

6. Move the master.mdf and mastlog.ldf files to the new location.

C:\> move “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf” F:\MSSQL\Data\ 1 file(s) moved.[SQLPROD1] C:\> move “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf” F:\MSSQL\Data\ 1 file(s) moved.

7. Restart the instance of SQL Server.

C:\> net start MSSQLSERVER[SQLPROD1] C:\> net start SQLSERVERAGENT[SQLPROD1]

8. Verify the file change for the master database.

18) Is it important to take the system database backups?

Yes, It is very important to take system database backups except tempdb.

19) Is it possible to take the tempdb database backup?

No, it’s not possible to take tempdb backup and it’s not required.

20) How to restore model database?

model Databse can be restores same as any other user defined database from its backup.

RESTORE DATABASE [model] FROM  DISK = N'E:\model.bak'

WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 10

GO

21) How to restore msdb database?

• Stop SQL Server agent service
• Run the below command to restore the msdb database.

RESTORE DATABASE [msdb] FROM  DISK = N'E:\msdb.bak' 

WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10 

GO

• Start SQL Server agent service

22) How to restore master database?

• Start the SQL Server Instance in single user mode using –m parameter in the SQL Server configuration manager for SQL Service.
• Issue the below command from command prompt to restore the master database.

23) Is it possible to rename any of system databases?

No, it’s not possible to rename any system database.

24) What is the default owner of master, model, tempdb and msdb database? Is it possible to change the owner of system databases?

Default owner of system databases is sa, We can’t change the default owner of master, model, tempdb and distributor databases.

25) What are the recommended Data files settings for tempdb?

• Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.
• Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs.
• Make each data file the same size; this allows for optimal proportional-fill performance.
• Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
• Put the tempdb database on disks that differ from those that are used by user databases.

26) What is recovery model in SQL Server?

SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

27) How many types of Recovery models we have in SQL Server?

Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

28) Difference between Full, Bulk Logged and Simple recovery model?

Recovery model	Description	Work loss exposure	Recover to point in time?
Simple	No log backups. (Only Full and Differential backups)Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.	Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.	Can recover only to   the end of a backup.
Full	Requires log backups.No work is lost due to a lost or damaged data file.Can recover to an arbitrary point in time (for example, prior to application or user error).	Normally none.If the tail of the log is damaged, changes since the most recent log backup must be redone..	Can recover to a specific point in time, assuming that your backups are complete up to that point in time.
Bulk logged	Requires log backups.An adjunct of the full recovery model that permits high-performance bulk copy operations.Reduces log space usage by using minimal logging for most bulk operations. For more information, see	If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.Otherwise, no work is lost.	Can recover to the end of any backup. Point-in-time

29) How can you setup a default recovery model for all newly created databases as simple?

We can setup the recovery model of model database as simple to make sure that newly created database’s recovery is automatically configured as simple.

30) How to change the recovery model of a database?

USE [master] 

GO 

ALTER DATABASE [AdventureWorks] SET RECOVERY SIMPLE 

GO

Note: It is always advisable to take a full backup of the database immediatelly after changing the database recovery model.

31) Recovery model of system databases?

System database	Recovery model	Comments
master	Simple	For backwards compatibility with earlier versions of Microsoft SQL Server, the   recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model.
model	User configurable	Newly created user databases use the same recovery model as the modeldatabase. If you want your new databases to use the simple recovery model, change the recovery model of model to SIMPLE.Best practice:  We recommend that you create only full database backups ofmodel, as required. Because model is small and rarely changes, backing up the log is unnecessary.
msdb	Simple (default)	If you want to use the backup and restore history information in msdb when you recover user databases, we recommend that you use the full recovery model formsdb. Additionally, consider placing the msdb transaction log on fault tolerant storage.
Resource	—	The recovery model is irrelevant. SQL Server backup cannot back up the Resourcedatabase.NoteYou can perform a file-based or a disk-based backup on the Resource database by treating Mssqlsystemresource.mdf as if it were a binary (.exe) file. But you cannot use SQL Server restore on these backups.
tempdb	Simple	The simple recovery model is required; therefore, tempdb log space is always automatically reclaimed. You cannot backup tempdb.