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!!!

RJ!!

How to take differential backup ? TIP #67

Friends,
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
BACKUP DATABASE IndiandotnetDB
TO DISK = 'D:\IndiandotnetDB.dif'
WITH DIFFERENTIAL;
GO
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!!!
RJ!!

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

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type 
       WHEN 'D' THEN 'Database' 
       WHEN 'L' THEN 'Log' 
   END AS backup_type, 
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

ORDER BY 
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

  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 !!

Rj!!

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

BACKUP DATABASE IndiandotnetDB
TO DISK = 'D:\Indiandotnet.bak'
WITH CHECKSUM;

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.

RESTORE VERIFYONLY
FROM DISK = 'D:\Indiandotnet.bak'
WITH CHECKSUM

I hope this might helpful you somewhere.

Thanks!!

RJ!!

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
BACKUP DATABASE INDIANDOTNETDB
TO DISK = 'E:\MyCompressedBackup.bak'
WITH COMPRESSION ;
You will get compressed backup.

I hope this will help you somewhere.
Enjoy!!!
RJ!!

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'
SELECT LEN(@Name)

2) LTRIM & RTRIM :-

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
SELECT LEN(RTRIM(LTRIM(@Name))) As LenghtWithLTRIMRTRIM

3) LEFT & RIGHT:-

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

4) LOWER & UPPER :-

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,
       LastName,
       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.

Enjoy!!!

RJ !!

Converting Row to Column use Pivot feature TIP #62

 

Problem:  Sometimes we may require to convert row data to column. we require pivot view of data.

Solution:  Lets understand this by an example below

I am using a table variable which have few columns like  Employee, Amount, Month and year. Lets define and create some sample data here.

DECLARE @tblEmployee AS TABLE (Employee        VARCHAR(50),
                           Amount DECIMAL(10,3),
                           intMonth       TINYINT,
                           intYear        INT)

INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '10000',1,2013)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '15000',2,2013)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '16000',3,2013)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '10700',1,2014)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '16000',2,2014)

Now just cross check the data how it look

SELECT * FROM @tblEmployee

Now we want result something like as shown in below figure

Now to achieve this we wrote following pivot syntax

SELECT Employee,
       intYear,
       [1] As Jan,
       [2] AS Feb,
       [3] As mar
FROM ( SELECT Employee,
       intYear,
       Amount,
       intMonth
       FROM @tblEmployee )p

PIVOT (SUM (Amount) FOR intMonth IN ([1],[2],[3]))Q

 

I hope this may help you somewhere.

In coming tips we would share how to create dynamic Pivot and  UNPIVOT.

Thanks & Enjoy !!!

RJ!!

Database Diagram support object cannot be install error-SSMS TIP# 61

 

Problem:-  It is general problem that you are interested in generating the database diagram of a database and when you right click on database diagrams folder of database and try to create a new database diagram you get following error as shown in below snap.

Solution:-

The above snap says that you are not authorize person to generate database diagram so to resolve this we have to do following steps and then you are good to go to create database diagram

step 1:- Go to database properties by right clicking on database and choose files option from left side bar your will get following screen.

If you see above screen you will find the owner text box is empty.

Step 2:-  Now click the button near by the textbox and you will get another popup window just select login “sa” user (or who has permission) as shown in below figure

Step 3:-  Once it is  selected  the owner text box will be filled  as shown in below figure now click on OK button

 

By following above 3 simple steps you will be able to create database diagram for particular database.

I hope this solution may help you if you stuck in such situation.

Thanks & Enjoy!!!

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,
st.Name,
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
GROUP BY GROUPING SETS (
   (st.Name,p.LastName + ',' +p.FirstName ),
   (st.Name),
   (p.LastName + ',' +p.FirstName ),
   (sod.OrderDate)
  
)
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 !!!

RJ!!!

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

Solution:-

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

SELECT o.name,
       i.name AS [Index Name], 
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
       s.auto_created,
       s.no_recompute,
       s.user_created
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 !!!

Rj!!

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
WHEN MATCHED THEN
UPDATE
SET Destination.FirstName = Source.FirstName,
    Destination.LastName = Source.LastName,
    Destination.Course = Source.Course
WHEN NOT MATCHED THEN
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.

Thanks!!!

RJ!!!

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 .

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 !!!

Good to Know SQL JARGON - An interview TIP #56

 

Hello friends,

Just wanted to share some technical words here again it is good to know

1) OLAP :  On Line Analytical  Processing

2) MOLAP :- Multidimensional  On Line Analytical Processing

3) ROLAP :- Relational On Line Analytical Processing

4) HOLAP :- Hybrid On Line Analytical Processing

5) RTOLAP :- Real Time On Line Analytical Processing

6) DOLAP :- Desktop On Line Analytical Processing

8) SSIS :- SQL Server Integration Services

9) SSAS :- SQL Server Analysis Services

10) SSMS :- SQL  Server Management Studio

11) SSRS :- SQL Server Reporting Services

12)Magic Table:- A temporary table use at the time of  trigger

13)DML :- Data Manipulation Language (Insert/Update/Delete)

14) DDL :- Data Definition Language  (Create /Alter /Drop)

15) DCL :- Data Control Language (Grant /Revoke)

16) TCL:- Transaction Control Language  (Rollback /Commit)

17) ACID :- Atomicity,  Consistency , Isolation ,Durability

18) PK- Primary Key

19) FK – Foreign Key

20) NF:- Normal Form (1NF,2NF,3NF)

21) ODBC :-  Open Database Connectivity

22) OLEDB :- Object Linking  and Embedding Database

23) RDBMS :- Relational Database Management System

24) CRUD:- Create, Read , Update, Delete

25) ETL :- Extract Transform Load

26) BI :- Business Intelligence

27) SP :- Stored Procedure

28) VLDB :- Very Large Data Base

29) CTE:- Common Table Expression

30) CDC :- Change Data Capture

31) TDE :- Transparent Data Encryption

I will add more in future.

Thanks & Enjoy !!!

RJ !!!

Maintenance of fragmented Heap table–TIP #55

 

Friends,

In last post  post #54  we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so.

Now our task is to run maintenance of this highly fragmented table.

So you have to run following command

“ALTER TABLE  TABLENAME REBUILD;

For example  I run the Rebuild for  Adventureworks2012 database’s heap table which we found in post #54.

See below snap for detail

 

So, with this REBUILD command the table is rebuild and the fragmentation issue resolved.

I hope it may help you some where.

Thanks & Enjoy!!!

RJ!!

What is Heap table in SQL Server and How to get all the heap table from a database ? TIP # 54

 

Heap table:-

A table without cluster index is called Heap table. Now you are thinking why we are talking about this.

So as  SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table.

Now our next question is how to determine all the heap table from a database

so below is simplest query to determine all the heap tables from a  database

SELECT T.Name 'Heaptable'
FROM sys.indexes I    
    INNER JOIN sys.tables T
        ON I.object_id = T.object_id
WHERE I.type = 0 AND T.type = 'U'

I ran this query on Adventureworks database and 2 tables with heap as shown in below figure

now in next post we will discuss how to rebuild fragmented heap table which is a maintenance activity.

I hope this article may help you.

Thanks !!!

RJ!!

How Enable /Disable all CONSTRAINT on a table ? tip #53

 

Dear Friends,

Although , When we have designed our database he/she knows what type of constraints is require to make data perfect and the Database designer applies those constraints but it might be possible that sometime we have to disable those constraints make some tweak with the data in the table and then again enable the constraints.

Below are two simple command to Enable & Disable constraints of a table.

a) Disable constraints on a table

ALTER TABLE  tableName NOCHECK CONSTRAINT  ALL

b) Enable Constraints on a table

ALTER TABLE TABLE_NAME CHECK CONSTRAINT ALL

Lets understand this by an example suppose, I have city_seed table which having Foreign key constraint of country_seed table.

Now  when we run disable constraint command as shown above then you will find the constraint is disable  as shown in figure

Now when we run enable constraint then see below figure

I hope it might helpful you somewhere.

Thanks & Enjoy!!!

RJ!!!

A hidden feature sp_MSforeachdb TIP #52

 

As we discussed yesterday a hidden feature tip #51 which is sp_MSForeachtable . sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB.

Sp_MsForEachDB  by the name it is clear that it will run provided command to each database.

The syntax of sp_MsForEachDB  is very simple almost like sp_MsForEachTable.

Lets understand this by an example

Suppose we want name of each database  of our SQL server for this we will run following command

Execute sp_MSforeachdb " SELECT  '?' AS Name"

Now  when we execute it we will get following result as shown in below figure.

This two stored procedures are not documented but it help a lot in maintenance task and other tasks.

I will share some maintenance query  with sp_MsForEachDB & sp_MsForEachtable  soon.

I hope this will help you somewhere.

Enjoy !!!

RJ!!!

A hidden feature sp_MSforeachtable–run on entire tables of a Database TIP# 51

Problem:-

Sometimes it happened that you need to run a single statement on entire tables which exists in database.  so most of the time we think of cursor which run for each sys.objects (table)  and we execute the dynamic statement by replacing table name.

Solution:

Although the solution we are thinking is correct there is no issue at all but SQL SERVER made our life more each by providing “sp_MSForEachTable”  which is a hidden stored procedure.

By the name it is clear that it will run on each table.

Lets understand this by an example. Suppose you want to  row count of each table then you can write following statement.

DECLARE @tblRowCount AS TABLE (Counts  INT,
                               TableName VARCHAR(100))

INSERT INTO @tblRowCount (Counts,TableName)
EXEC sp_MSforeachtable
@command1='SELECT COUNT(1) As counts,''?'' as tableName FROM ?'

SELECT * FROM @TblRowCount ORDER BY Counts desc

Now when we run it we will get row count of each table as shown in below snap

 

A part from it you can use this hidden feature in maintenance also like running re indexing, re organizing entire table index etc.

I hope this may help you some where.

Enjoy!!!

RJ!!