Walking with SQL Server

In SQL Server, there are three types of backups: Full Backup: Full backup means it will take the backup of the total .mdf file. Differential Backup : Differential backup means it will take the backup of the extent's (8 pages= 1 extent) data which has been changed since last full backup. It is cumulative in nature and so is also called cumulative backup. cumulative means it will take backup from last full backup. T-log Backup: T-log backup means it will backup the transaction log file. It is not cumulative in nature. Now, there are three Recovery models in SQL server which are defined as: Simple Recovery Model: In this recovery model, we can only have full backup and differential backup. If the data needs to be restored for the database having simple recovery model then last full backup will be restored first and after that the last differential backup. There will be no point in time recovery in this case. Full Recovery Model: In this recovery model, we...

When crash happens, the recovery of uncommitted and committed transaction is done by using transaction log. Transaction Log Architecture There are basically two types of transaction log architectures: Physical : In physical architecture virtual log files are maintained for different transactions. Virtual log files are maintained for easy management. Logical : In logical architecture LSN is maintained. Recovery always starts from last uncommitted transaction or checkpoint whichever is less. Last uncommitted transaction is known as MIN LSN Checkpoint Checkpoint occurs in two conditions: 1. Automatic : It will occur automatically in either of the two following cases(whichever is lower) Recovery Interval 70% log is full 2. When an activity happens in database : It will occur if any of the following case happens When we write explicitly checkpoint in query window When new db is created When backup is created When SQL server shuts down The recovery is not ...

There are basically three set of statements: DDL: Data Definition Language - Create and Alter statements fall in this category DML: Data Manipulation Language - Insert, Update and Delete statements fall in this category DCL : Data Control Library - Commit and Rollback statements fall in this category DDL and DCL are permanent in nature and DML statements are actually treated as transactions. Transactions should follow atomicity i.e. All or none property i.e. if execution happens it will happen for all the statements in a transactions or else it would not happen for anyone. In SQL server every command or statement(DML) is treated as one transaction. So there are basically two types of transactions: Implicit: Where we do not need to add any keyword from our side to begin and end a transaction. SQL server itself adds it at the beginning and completion of each statement. Explicit:    Where we need to add "Begin Tran" at the beginning and 'com...

Till 2005 version of SQL, NTAUTHORITY\ADMINISTRATORS was there. It was there, because this group knew who are the administrators on the system. We didn't need to create a group or user here. Whoever would be the administrator of windows will be able to enter SQL Server without any login creation. But customers did not wanted it, so it was removed in 2008 version. So, now if customer wants to not have this feature in 2005, we have to delete NTAUTHORITY\ADMINISTRATORS and then create login for whatever user they want explicitly. Protocols of SQL Server(For Network) TCP\IP(Transmission Control Protocol/Internet Protocol) NP(Named Pipes) LPC(Local Process Communication) or Shared Memory VIA(Virtual Interface Adapter)-depleted or very rarely used Shared Memory or LPC When client is present on the same machine where SQL Server is installed. It means client and server both are on the same box. Named Pipes It is used only in case of intranet. TC...