Tag Archives: SQL Server

ACID Properties

In the context of database transaction processing, the acronym ACID refers to the four key properties of a transaction:

Atomicity
• Consistency
• Isolation
• Durability

Atomicity : All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.

Consistency :  data is in a consistent state when a transaction starts and when it ends.For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation :  The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized.For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

Durability : After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

Advertisements

Types of SQL Server Backup -1 : FULL Backup

The most common types of SQL Server backups are complete or full backups, also known as database backups.  These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup.

A full backup can be completed either using T-SQL or by using SSMS

Create a full backup of the AdventureWorks database to one disk file

T-SQL

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'
GO

SQL Server Management Studio

  • Right click on the database name
  • Select Tasks > Backup
  • Select “Full” as the backup type
  • Select “Disk” as the destination
  • Click on “Add…” to add a backup file and type “C:\AdventureWorks.BAK” an click “OK”
  • Click “OK” again to create the backup

SQL Server – Hide system objects in Object Explorer – SQL Server Management Studio

By default, SQL Server system objects are listed in Object Explorer in Management Studio. These system objects include system database, system tables/views/procedures and so on.

SQL Server Management Studio provides an option to hide these objects from Object Explorer to prevent *accidental* use.

To enable this option follow below steps.

Step1: Go to Tools > Options

image

Step2: Navigate to Environment > Startup

image

Step3: Check Hide system objects in Object Explorer and click OK:

image

Step4: An confirmation message will be displayed saying that changes will take effect after Management Studio is restarted, click on OK to continue:

image

 

Once you restart SQL Server Management Studio, you will notice that system objects are no longer listed in Object Explorer:

What is hidden by this setting:

1. System databases – This is not hidden for SQL Server 2012
2. System Stored Procedures
3. System Tables
4. System Views