Advanced SQL tutorial pdf, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, Resource Database, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL Server Resource Database, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions
SQL SERVER Resource Database – Deep Dive
Many people don’t know how many system databases are present in one instance of SQL Server. Well four are visible but one is hidden. They are-
You can easily check these under System Databases in SSMS. Today I will focus only on resource database. I have been asked this question many times in technical interviews. Please go through the to the point information about resource database.
1. The Resource database is a read-only database that contains all the system objects that are included with SQL Server.
2. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
3. The Resource database does not contain user data or user metadata.
4. The Resource database is used in upgrading to a new version of SQL Server an easier and faster procedure.
5. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.
6. The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in :\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binn\ and should not be moved.
7. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.
8. Upgrades and service packs sometimes provide a new resource database which is installed to the BINN folder. Changing the location of the resource database is not supported or recommended.
9. SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.
–To access SQL definitions of system objects, use the OBJECT_DEFINITION function:
-- SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects')); GO --
Cheers, Thanks for reading !
Pingback: SQL Server Interview Questions – Set 10 (10 Questions ) | Improving my SQL BI Skills