ALL SYSTEM DATABASE IN SQL SERVER 2008

Understanding system database in SQL Server 2008

There are flowing types of system database in SQL server 2008.
1.    Master Database.

The master Database records all the server-specific configuration, including authorized user, database, system configuration settings, and remote server. In addition, it records the instance-wide metadata, such as logon accounts, endpoints, and system configuration settings.
                The Master database is the Main DB of SQL Server. It actually records all the system level info's. Every instance of SQL Server has its independent Master db; because it has to  record instance level configuration information. The information being captured in the Master database includes SQL Server instance level configurations,  SQL Server Logins, Service Broker Endpoints, System Level Stored Procedures,System level Functions ,linked server configurations,etc.
Apart from that The master database  stores the initialization information of the SQL Server. Therefore if the master database becomes unavailable, the SQL Server database engine will not be started.
. The Master database  contains two physical files, namely master.mdf (data file) and mastlog.ldf (log file). By default when you are installing SQL Server 2008 the data and log file for master DB are installed in the following folder location Drive:\ProgramFiles\MicrosoftSQLServer\MSSQL10.MSSQLSERVER\MSSQL\DATA\
If master database gets corrupted the SQL Server Service will not start.

2.    Temp Database.
The temp Database as the name suggest is a temporary database.
 It holds all temporary tables and stored procedures. 
Sql Server usese isr to resolve large or nested queries,
This is also being used while sorting data before displaying the results to the user, online indexing, BCP.

Temp database  store's temporary tables (#temptable or ##temptale), table variables, ,row versioning, create or rebuild indexes sorted in TempDB, cursors, work tables etc. Each time the SQL Server instance is restarted all objects in tempdb are destroyed, so permanent objects cannot be created in this database. 
Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state.
To Shrink TempDb either, restart SQL Server or Kill Transaction, or one can use shrink DB Command
3.    Model Database.
The model database works as a template or a prototype for the new database. Whenever a database gets created, the contents of the model database get copied to the new database.
In this database, one can set the default values for the various arguments to be specified in the Data Definition Language (DDL) Statements to create database objects. 
in addition, if some one  want every new database to contain a particular database object, you can add the object to the model database. 
Each time when new Db gets created the added objects also gets created.
UD tables, user defined data types, SP, UDF etc can be created in the Model database and will exist in all future user defined databases.
The database configurations such as the recovery model (Simple Full or Bulk Logged)for the Model database are applied to future user defined databases

4.    Msdb Database.
The Msdb database  is used to support the SQL Server Agent. 
The SQL Server Agent is a tool that Schedules periodic activities of the SQL Server, such as backup and database mailing, External processes, DTS, SSIS and Job excution, scheduled indexing, System DB Based scheduled operations.
 The Msdb database contains task scheduling, exception handling, alert management, and system operator information . The Msdb database contains a few system-defined tables that are specific to the database.  
            Additional Information 
•    Provides some of the configurations for the SQL Server Agent service 
•    For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database.
Inside MSDB Datbase one can store SSIS Packages too lin the similar directory structures such as file systems.

5.    Resource Database. 
The resource Database is a read-only database containing all the system objects. this has  system-defined procedures and views that are included with SQL Server. The Resource database is responsible for physically storing all of the SQL Server  system objects. This database has been added to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.

 

SQL Server 2005's 5th system database - MSSQLSystemResource (The Invisible Database)

MSSQLSystemResource is a database that complements the master db. It is like the name smartly impels a resource database.
All system stored procedures, views and functions are stored here.

This database is hidden from the user. We can't view it in Object Explorer or with the use ofsp_helpDB or by selecting from a sys.databases view. Resource database does not contain any of user data. This database has to be backed up using file-based backup or by using Drive Backups.

So how do we know its presence?
-Go to the Data directory of your SQL installation [Install Drive]:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and there you will find the MSSQLSystemResource.mdf andMSSQLSystemResource.ldf.

If you want to see what is there in this database:

1. Stop the SQL Server service.
2. Copy both files and rename the copies to MSSQLSystemResource_Test.mdf andMSSQLSystemResource_Test.ldf.
3. Start the SQL server service
4. Attach the new files.
5. Query the database.

 

Resource Database in SQL Server 2008

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sysschema of every database. The Resource database does not contain user data or user metadata.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. 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. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.

Physical Properties of Resource:

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\MSSQL10.<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.

Backing Up and Restoring the Resource Database:

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.

Note: After restoring a backup of mssqlsystemresource.mdf, you must reapply any subsequent updates.

Accessing the Resource Database:

The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The ID of the Resource database is always 32767. Other important values associated with the Resource database are the version number and the last time that the database was updated.

To determine the version number of the Resource database, use:

SELECT SERVERPROPERTY('ResourceVersion');

GO

To determine when the Resource database was last updated, use:

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

To access SQL definitions of system objects, use the OBJECT_DEFINITION function:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

GO

6.    Distribution Database.
(Importance with merging and replication in Sql Server)
Primary data source to support SQL Server replication, The Distributor is a server that contains the distribution database, which stores metadata and history data for all types of replication and transactions for transactional replication. To set up replication, you must configure a Distributor. Each Publisher can be assigned to only a single Distributor instance, but multiple publishers can share a Distributor.
Actullay there are many ways to configure replication, which can be a further topic of discussion.

7. ReportServer Database.
This is the Primary database for Reporting Services to store the Meta data and object definitions 
A Reporting Services application uses two SQL Server relational databases for internal storage. By default, the databases have names as  ReportServer and ReportServerTempdb. ReportServerTempdb is created with the primary report server database and is used to store temporary data, session information, and cached reports.

The Db has very specific role when you are adding new types of role on your report server and modeling the sceurity over the current report server security architecture.

CREATE DATABASE AND KNOW ABOUT FILES AND GROUPS OF A DATABASE

Follow  This Link:-

http://msdn.microsoft.com/en-us/library/aa258257(v=sql.80).aspx

What Is Piecemeal Restore?

Piecemeal restore is a process which allows databases that contain multiple filegroups to be restored and recovered in stages.

Which Version of SQL Server supports Piecemeal restore?

Piecemeal restore was introduced in SQL Server 2005 and is supported in SQL Server 2005 and later versions.

What are the Limitations?

The Database should contain multiple files or filegroups and should have at least One Read-Only filegroup.

Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

Types of Piecemeal Restore?

• Offline
  In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.
  All editions of SQL Server 2005 and above support offline piecemeal restores.
• Online
  In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
  SQL Server 2005 Enterprise Edition and later versions support Online piecemeal restores.

 

 

What is Transaction and Transaction properties (ACID)

What is Transaction?
There can be many ways of defining the same... however, the most simple could be as follows:
A transaction is a logical unit of work in which, all the steps must be performed or none.

Once we understand "what is transaction"... next is to understand "the properties" exhibited by transaction.
There are FOUR important properties of a transaction:
1. Atomicity
2. Consistency
3. Isolation and then
4. Durability

ATOMICITY - Any... for that matter any database modifications must follow an “all or nothing” rule... which means: If one part of the transaction fails, the complete transaction fails. Each transaction is said to be “ATOMIC”. It is critical that SQL Server maintains the atomic nature of transactions... not only SQL Server any DBMS, operating system or hardware failure.

CONSISTENCY - States that only valid data will be written to the database. In simple words if the database was consistent before the execution of the transaction then it should remain consistent after the complete execution of that transaction.

For some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.

On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.

ISOLATION - First, let me put it in straight words: The transaction should not be interfered by any other transaction executing concurrently.

Now that means - multiple transactions occurring at the same time not impact each other’s execution. For example, if User A issues a transaction against a database at the same time that User B issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform User A's entire transaction before executing User B's or vice-versa.

This helps in preventing User A's transaction from reading intermediate data due to User B's transaction that will not eventually be committed to the database.

We  always get into confusion w.r.t the way we describe isolation. We should alyways remember that isolation property does not ensure which transaction will execute first, it is just that transactions will not interfere with each other.

DURABILITY - means any changes made by the transaction should be permanently committed in the database. This ensures that any transaction committed to the database will not be lost in spite of any subsequent software or hardware failures.

To ensure Durability is at its best we make use of database backups and transaction logs that facilitate the restoration of committed transactions.

Password Protected A Database Backup File

            -------Starting With Create a Database---------
Note :-This process possible with only  commands not with (SSMS) wizard.

             [Use Master]
        Create database P_Protected
                       
             --Take a Full Backup with password---
BACKUP DATABASE P_Protected TO DISK = 'D:\P_Protected.bak'WITH PASSWORD = 'Demo'
---------------- Restore Database With Replace or NoRecovery Option---

Restore  DATABASE P_Protected from DISK = 'D:\P_Protected.bak'   With Replace,   norecovery, PASSWORD = 'Demo'
--------------------Last Step  Restore Database With Recovery-------------

Restore  DATABASE P_Protected from DISK = 'D:\P_Protected.bak'   With  Recovery , PASSWORD = 'Demo'

----------------------If You Try with Wrong Password then you get an Error --------
Restore  DATABASE P_Protected from DISK = 'D:\P_Protected.bak'   With Replace,   norecovery, PASSWORD = 'Demo1'


Question 1: What happens if you give a wrong password or try to restore without giving password?

Answer: The restoration will fail with the below error

Msg 3279, Level 16, State 2, Line 1  Access is denied due to a password failure  Msg 3013, Level 16, State 1, Line 1  RESTORE DATABASE is terminating abnormally.

Question 2: How will you come to know if a backup is password protected?
Answer: When you try to restore, it will give an error saying "Access is denied due to a password failure"
And when you try to execute RESTORE HEADERONLY, the file name will be shown as "*** PASSWORD PROTECTED ***"

RESTORE HEADERONLY FROM DISK='D:\PROTECTED.BAK'

SQL Server: Top 10 Secrets of a SQL Server Expert

Follow this link
http://technet.microsoft.com/en-us/magazine/gg299551.aspx

SQL Server Full Backup and Differential Backup with Restore Option

------SQL SERVER FULL BACKUP AND DIFFERENTIAL BACKUP WITH RESTORE OPTION--

--FIRST THING TAKE  A FULL BACKUP OF A DATABASE THERE IS DEMO IS MY DATABASE NAME--

 1) BACKUP DATABASE DEMO TO DISK  ='C:\DEMO_FULL.BAK' --RUN THIS F5

 2)--Insert some data into a table that belong to your database--

  INSERT asd VALUES('ASHISH','MOH',3,4,3333)
  INSERT asd VALUES('malwal','chd',32,42,33323)

  3)  -- Now take Differential Backup--

  BACKUP DATABASE DEMO TO DISK  ='C:\DEMO_DIFF.BAK'WITH DIFFERENTIAL
 
  ---------Now restore full backup and Differential Backup---

  4 --First restore full back its mandetory for restore any differential backup or any log backup --

 5  Delete asd

   SELECT * FROM dbo.asd

6----Now restore FullBackup WITH REPLACE Norecovery

 RESTORE DATABASE DEMO FROM DISK ='C:\DEMOFULL.BAK' WITH REPLACE , Norecovery

 --After finish this command refresh your database and you can see a up green arrow point on your database
  --it means  your database in restoring mode and you can restore differential backup or log backup

7)   RESTORE  DATABASE DEMO FROM DISK  ='C:\DEMO_DIFF.BAK'  WITH RECOVERY

    8) ---- Execute your table now--

    SELECT * FROM dbo.asd