SQL SERVER –FIX- Error- Failed to connect to an IPC Port: The System cannot find the specified path.- Database Engine Tuning Advisor (DTA).

Today when I was working on a Performance Tuning Project, I tried to open the Database Engine Tuning advisor, I got an error,” Failed to connect to an IPC Port: The System cannot find the specified path”. This is a common error if we have previously stopped the DTA(when it is executing)  or disconnected from the network  while DTA was running.

We can resolve this issue by,

Open windows task manager -> under that kill DTASHELL.EXE.

After doing this Fix, we can simply connect the Database Engine Tuning Advisor without any error .


SQL Server – How to backup a database?

Earlier post we learnt what the different types of database backups in SQL Server. Today we are look into how to take a backup in SQL Server?

There are mainly  two ways to backup the database, i.e.

  • Using management studio
  • Using  T-SQL
  • Or using any third party tools.

Using management studio:


Right click on the database -> choose tasks option -> choose Backup option.

Look the figure given below.


In backup option window, there are some parameters which we need to choose,

Database Name: name of the database to be backed up.

Recovery model of the database: Full/Simple/Bulk

Backup Type: Full/Differential/Transaction log

Backup Component: Which database component we need to backup? I.e. database/file/file groups

Backup set: A backup set will contain backup from single successful operation.

Backup Destination: where we need to take backup? To Disk or Tape


Backup using T-SQL:

--Full Backup
Backup database Mydatabase to disk = 'C:\testbakup.bak'
--Differential Backup
Backup database Mydatabase to disk = 'C:\testbakup_differntial.bak' with differential 
--Transactional Log Backup
Backup log Mydatabase to disk = 'c:\test_log.trn'

Backup using Third party tool:

We can use some third party tools for backing up of  sql server database


SQL SERVER – Introduction to BACKUP in SQL Server

What is the primary job of a DBA? Answer is simple Backup and Recovery. Without database no DBA can exists. Today I am  going to explain about the backups in sql server. The purspose of Backup is to recover the damaged database.

Full Backup:

A fullbackup is the one which backup all the data in the database and also enough transaction log for the recovery of the database.

Recovery Model : Full,Bulk,simple

Differential/Differential base Backup:

A differential backup is the one which contain only the changes after successful Fullbackup. This backup will really improve the performance of the database. So we need to restore the latest restore the latest differential backup during the recovery process.

Recovery Model : Full,Bulk,simple

Backup Types:

Partial Backups and Differential Partial Backups

These backups are designed to provide more flexibility for backing up databases that contain some read-only filegroups under the simple recovery model. However, these backups are supported by all recovery models

Partial Backup:

A backup of all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups. A partial backup of a read-only database contains only the primary filegroup.

Differential Partial Backup:

A backup that contains only the data extents that were modified since the most recent partial backup of the same set of filegroups

File Backups:

The files  in the database  can backup and  restored individually.If the database contains contains different files, located in different disks and if one of the database file is corrupted in a disk, we can restore the database  file in the particular disk.

Different types of File Backups:

Full Backup

Backup of all the data in one or more files, or filegroups.

Differential Backup

A backup of one or more files that contain data extents that were changed since the most recent full backup of each file.

Transaction Log Backups

Each log backup covers the part of the transaction log that was active when the backup was created, and it includes all log records that were not backed up in a previous log backup. An uninterrupted sequence of log backups contains the complete log chain of the database, which is said to be unbroken. Under the full recovery model, and sometimes under the bulk-logged recovery model, an unbroken log chain lets you to restore the database to any point in time.

Without transaction log, we can’t recover the database to point in time recovery.

Recovery model: Full, Bulk