How to take Transaction Log backup ? TIP# 68


As we discussed in earlier tips how to take full backup and differential backup now in this tip we will see how to take transaction backup .

Before taking transaction backup the first and mandatory condition is that you should have a full backup.

Just right click the database and go to backup option as you will get following screen. Now we need to select Transaction Log   option in   backup type dropdown.

Once this is selected we need to give a transaction log backup name as shown below. The extension should be .TRN




Once you provided all the mandatory values just click on then you will get your  Transaction Log backup.

We can take Log backup with  SQL command also

For example :-

BACKUP LOG IndiandotnetDB
TO DISK =’E:\IndiandotnetTransaction.trn’

When you run above command you will get following screen and your log backup is done.


I hope this will help you.

Thanks & Enjoy!!!


How to take differential backup ? TIP #67



We were discussing backup from last few post. Now lets take a look one more feature of backup which is differential backup.

Differential backup  is backup after last full backup. Before taking Differential backup  it is a mandatory condition to take full backup.

Differential backup can speed up your recovery process or we can say restore process.

In below example we would understand how to take differential backup

To take differential backup we can run following command

TO DISK = ‘D:\IndiandotnetDB.dif’

Or we can take differential backup by following steps also

Right click the database select database backup option you will get same screen which we explain at the time of full backup.

if you see below screen you will find we have change backup type to Differential and  taking backup to d drive with name Indiandotnetdb.dif


Once you click OK button. your differential backup will be completed.

I hope this tip might help you somewhere.

Thanks & Enjoy!!!


Oh! I forgot When I took last Database Backup ? How to determine this? TIP # 66


In last few tips TIP #64 & TIP #65  we get information  how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup.

Problem:-   Sometimes , we are interested to know when last backup is taken for particular database ?

Solution:  The first thing I want to share here that  you should have proper maintenance plan to take backup of database and by job History you are able to know when last backup is taken but if this is not the case then not to worry SQL Server maintain itself information related to backup. to determine this you need to write a simple query as shown below

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
   CASE msdb..backupset.type 
       WHEN ‘D’ THEN ‘Database’ 
       WHEN ‘L’ THEN ‘Log’ 
   END AS backup_type, 
   msdb.dbo.backupmediafamily.physical_device_name, AS backupset_name,
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id


  When I run above command I get information related backup like databasename, backup start date, Backup finish date, expiry date(if any) , backup type like whether it is database backup or Log backup , size of backup , path where database is taken, name of backup set.

see below snap for detail


I hope this tip might help you somewhere.

Thanks !!


Did I take right backup ! how to ensure backup can be restore ? TIP # 65


Problem:- We have seen last time how to take backup in tip # 64.   but sometimes it happened we took backup  and we are not able to restore it. It might be corrupted.

Now , Next step thought come in our mind how to ensure we took right backup which can be restore.

Solution:-  To ensure backup is correct. We can check following option as shown in below figure.

in Reliability section  check following option

1) Verify backup when Finished

2) Perform checksum before writing  to media


Once this option is check SQL server automatically cross check verify the backup when it is finished and by checking “Verify backup when Finished”

With CheckSum SQL Server cross check before taking backup.

We can also write following query

TO DISK = ‘D:\Indiandotnet.bak’


Now to assure more we can write following command and verify whether the database can be restored or not whether the backup set is valid or not.

FROM DISK = ‘D:\Indiandotnet.bak’


I hope this might helpful you somewhere.



How to take compressed backup in SQL Server TIP #64


Problem:-  A good maintenance plan always said to take backup but sometimes it is very difficult to take backup of database just because of space constraint.

We take backup but we don’t have enough space available.

Solutions:  SQL server provided  the best way and gave us solution of compressed backup. Now how to take compressed backup let see step by step here.

Step 1:-  Select the database whose backup you want. Just right click and select backup option as shown in below figure


Step 2:-  Once you click the backup option an new screen will appear as shown below


Step 3:-  Now click on  Add button as shown in above screen. Once you click on the add button a new screen will appear as shown below where you have to provide the path and file name of compressed backup file.


Step 4:-   Once you provided the filename click OK to the button now click on the options menu on right hand you will get new tab option here in the last you will get compress option as shown in below figure with arrow. Select compress backup option in dropown

once this done click on OK button now you are good to go. Your compressed backup is done. and you will get backup complete message as shown below


Or else you can use following command

TO DISK = ‘E:\MyCompressedBackup.bak’

You will get compressed backup.


I hope this will help you somewhere.



Various available string functions in SQL Server TIP # 63


There are various functions available in SQL Server and it is good to know all of them you never know when they will be helpful to you.

So lets start one by one.

1) LEN :-   By the name it is clear that LEN function give length of the parameter

For example :-

DECLARE @Name AS VARCHAR(100) = ‘Rajat’


By the name it is clear that both LTRIM  & RTRIM trim the white space from the parameter value.
LTRIM  do left trimming & RTRIM do Right side trimming.

For example :-

DECLARE @Name AS VARCHAR(100) = ‘     Rajat   ‘
SELECT LEN(@Name)  AS LengthWithSpace



LEFT and  RIGHT both the function provide part of the parameter according to provided length.
For example we have taken 3 characters from Left of "Rajat" which is "Raj" and with RIGHT function we got 2 letters from Right which is "AT"

For example:-

DECLARE @Name AS VARCHAR(100) = ‘Rajat’

SELECT LEFT (@name,3) AS LeftFunction
SELECT RIGHT(@Name,2) As RightFunction



By The name it is clear that you can change case of provided string parameter.

For example
DECLARE @Name AS VARCHAR(100) = ‘I am Rajat Jaiswal.’

SELECT LOWER(@Name) AS LowerCase
SELECT UPPER(@Name) AS  UpperCase


5) CONCAT :-

This is a new function introduce in latest versions. It concatenating all the provided parameters.
The best part is it handle NULL value also.

As shown in below figure we are concating FirstName, LastName, MiddleName in a new column name FullName.

and if you see below figure you will find the MiddleName have some  null values also but we did not do anything for that concate function handle it himself.

SELECT FirstName,
       MiddleName ,
       CONCAT(LastName,’,’ ,FirstName, ‘ ‘, MiddleName) As FullName 
FROM person.Person



I hope this might be useful for any new SQL person. There are many other functions which I will describe in  next post.


RJ !!

Grouping Sets–Good to know feature TIP# 60


Grouping sets is one of the cool feature came in SQL SERVER 2008. Lets understand here with problem and solution.

Problem:-  Suppose , We want  an aggregation result in a query with different groups. Firstly we want aggregated result on first column then combination of First & second column then other column combination.

So, to resolve this problem a basic traditional way is to create 3 separate query and combine there result.

Solution:-  Now in SQL Server 2008 onwards we have a new feature for to achieve such problem which is called GROUPING SETS.

Lets understand this by an example.

I am taking here Adventureworks2012 database. Now we want total due amount on different basis  example

1) total due amount on Territory name and sales person basis

2) Total due amount on  Territory name

3) total due amount on sales person basis

4) total due amount on sales order date  basis

To achieve above  results we write following query

SELECT sod.OrderDate,
p.LastName + ‘,’+ p.FirstName  As SalesMan,
SUM(sod.TotalDue) as totalDue
FROM [Sales].[SalesOrderHeader] sod
INNER JOIN [Sales].[SalesPerson] sp ON sp.BusinessEntityID = sod.SalesPersonID
INNER JOIN [HumanResources].[Employee] emp ON emp.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [Person].[Person] p ON p.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [sales].SalesTerritory st ON st.TerritoryID = sod.TerritoryID
   (st.Name,p.LastName + ‘,’ +p.FirstName ),
   (p.LastName + ‘,’ +p.FirstName ),
ORDER BY  st.Name,sod.OrderDate

Now when we run the query and we get results which we want.


I hope this may be help you some where.

Thanks !!!


Find last statistics updated date detail ?–Maintenance TIP #59


Problem:- One of the pain point in any SQL engineer  is “Performance”. There are various reasons due to which your SQL Server database is slow.

One of the possible reason is your maintenance.   You don’t know when statistics last updated and take further step if those are not updated


Here we have simple query to find when the statistics was last updated for a table.

SELECT, AS [Index Name], 
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)   ON i.[object_id] = s.[object_id]
                      AND i.index_id = s.stats_id
WHERE o.[type] = ‘U’
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

When you run it you will find last statistics update date if it is too old it means you have to run the maintenance for those tables.

see below screenshot which I run on my machines adventureworks2012 database.


I am sure you will analyze your database tables stats and run maintenance accordingly.

I hope this tip may help you some where.

Enjoy !!!


Merge Statement one of the way to synch destination from source table TIP# 58


Problem:-  Suppose you have a source table and one destination table and you want to synch destination table from source table. means if record is already exists then you need to update and the record with latest value and insert the record if record is not exists.

Solution:-  Although there are various ways exist but here we are using a new feature came in SQL Server 2008 which is MEGE statement.

Suppose we have a  tblStudentSource table and one tblStudentDestination table

both have equal columns as shown in below figure.

both have studentId, Firstname, LastName & course column



if you see above figure you will find that destination table there are 2 rows while in tblstudentSource table we have 3 rows.

Now if you see in tblStudentDestination LastName of StudentID  2 is spelled wrong.

So what we have to do here. We have to Insert a new row from tblStudentSource and update existing row.

To achieve this we will use merge statement as shown in figure


Now if you see above snap  we write basic merged statement which check condition of tblSource and destination table and if condition is matched then we wrote update statement if not matched then we wrote insert statement.

MERGE tblStudentDestination AS Destination
USING tblStudentSource  AS Source
ON Source.StudentId = Destination.StudentId
SET Destination.FirstName = Source.FirstName,
    Destination.LastName = Source.LastName,
    Destination.Course = Source.Course
INSERT (FirstName,LastName,Course)
VALUES (Source.FirstName,source.LastName,Source.Course);


Now we run above statement we got the desire result and our source and destination table is in synched.

I hope this post may help and give you a general Idea of merge statement.



What is TVP (Table Value Parameter) & How to use it ? TIP #57


This is one of the interesting feature which I like most. Instead of passing values from collection one by one pass entire collection to stored procedure as a table value parameter.

I know above statement is not digestive enough so lets understand this by an example.

Suppose , I have a table tblStudent with 4 columns studentId, FirstName, LastName, and class  as shown in below figure


Now I need to insert value in this table so I have created a simple stored procedure as shown in below figure


Now , I can easily insert values into it by calling the stored procedure for each student FirstName, LastName, course,

Suppose , I need to insert 4 records in the table then I need to call this stored procedure 4 times as shown in below figure


Now think you have .net program and you have to call this stored procedure more than 100 times Smile .

I know you are bit scary.

Not to worry thanks to SQL Server again by providing Table value parameter to handle such kind of situation.

Now let me explain how to create table value parameter and call it to resolve such problem step by step

Step 1:-

To create Table value parameter you have to  choose  user define data type  as shown in below figure


Step 2:-

Now you can modify the template script in my case I am creating a student type User defined table type as shown in below figure . You will find I am using all the column which I need to insert into tblStudent table



Step 3:-

Now I am writing a  new stored procedure which use this table type as shown in below figure


You find in the above snap we have created a procedure with student type parameter which we have created .

The point to remember here that the table value parameter should be READONLY when passes in a stored procedure

Step 5:- Now let see how to call this stored procedure which has student type table value parameter


Now when we run above snap statement we inserted 3 rows in tblStudent.

Now lets consider the above problem calling stored procedure 1000 times instead of that you will call stored procedure only once with this amazing Table value parameter feature.

You can pass any collection and data table as a parameter from .NET.

Isn’t it made our life easy ?  I am big fan of this feature.

I hope if you use you will also.

Hope this tip may help you somewhere.

Thanks & Enjoy !!!

RJ !!!