Post reply

Resourcedb vs systems databases

  • July 5, 2011 at 10:01 pm

    #240439

    Hi,

    What are the major differences between resourcedb and system databases, though both contains systems objects. How a resourcedb is different from system databases. How resourcedb can be useful ?

    Thanks in advance.

  • July 5, 2011 at 10:23 pm

    #1348729

    [font="Times New Roman"]

    Hi,

    System databases for SQL Server 2005 includes master tempdb, msdb and model databases.you might have noticed that there is no resource databse in SQL Server 2005 as all the system objects i.e. system table, system store procedure etc were present in the master databse.

    but in SQL Server 2008 all the system objects grouped into a database namely Resource database. the logic behind this seperation, i understand is user can't modify as well as drop the system objects.

    when you upgrade the SQL Server instance all objects in the resouce database are updated.

    let me know if you have any concerm.

    Best Regards,

    Anil Kumar

    Infosys

    [/font]

  • July 5, 2011 at 11:54 pm

    #1348749

    But as per my knowledge there is resourcedb in SQL 2005 too. If you go default data directory you will find mssqlsystemresource.mdf and mssqlsystemresource.ldf. I think these two files belong to resourcedb database if I am not wrong.

  • July 6, 2011 at 12:32 am

    #1348755

    [font="Times New Roman"]

    Please be informed that resource database included in SQL Server 2005. in SQL Server 2000 there was no resouce database. mssqlsystemresource.mdf and mssqlsystemresource.ldf files belongs to Resource database.

    Thanks,

    Anil Kumar

    [/font]

  • July 6, 2011 at 1:18 am

    #1348770

    anil_kumar32 (7/5/2011)

    System databases for SQL Server 2005 includes master tempdb, msdb and model databases.you might have noticed that there is no resource databse in SQL Server 2005 as all the system objects i.e. system table, system store procedure etc were present in the master databse.

    SQL 2005 had the resourceDB. In fact, it was SQL 2005 where it was introduced.

    The ResourceDB (in 2005 and 2008) contains the definitions of all the system procedures, views and functions. sys.objects is a view defined in the resourceDB. sp_help is a system procedure defined in the resourceDB. And so on

    None of those system objects are defined in master.

    Master contains server-wide information, like logins, linked servers, server-level permissions, DB information, etc.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply