SQL Server-BackUp History

Many times we need backup history, when which type of backup is taken?

Is back up was compressed? So I think let me make a Script which gives list of backup history.

Let me share One interesting fact which I observed when I was working on  this Script.

Let me describe scenario.


  • We have two server 1.Test server and 2.Local server
  • I need to restore database on Local serer from Test server.
  • But Database does not exist on Local server.
  • So I will create new database.Then Restore Full back up which is taken from Test server.
  • now i will run following script for newly created and restored database.
  • Note it will show one record for which database is restored.
  • Reason is very simple cause it is backup right now for newly created Database as well as it is for Test server.as we have restored database to Local server from backup.so it will show one record for backup history on Local server without taking backup.
SELECT  ms.software_name [software] ,
		s.user_name ,
        s.database_name ,
        CASE ms.is_compressed
          WHEN 1 THEN 'Compressed'
          ELSE 'DeCompressed'
        END AS [BackupMethod] ,
         s.recovery_model ,
        CASE s.type
          WHEN 'D' THEN 'FULL'
          WHEN 'L' THEN 'Transaction Log'
          WHEN 'I' THEN 'Differential'
        END AS BackupType ,
        m.physical_device_name ,
        CASE ms.is_compressed
          WHEN 1
          THEN CAST(s.compressed_backup_size / 1048576 AS NUMERIC(18, 2))
          ELSE CAST(s.backup_size / 1048576 AS NUMERIC(18, 2))
        END AS [Size in MB] ,

        s.backup_start_date ,
        CAST(DATEDIFF(s, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) TimeTaken ,
        s.first_lsn ,
        s.last_lsn ,
        s.server_name
FROM    msdb.dbo.backupset s
        INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
        INNER JOIN msdb.dbo.backupmediaset ms ON ms.media_set_id = s.media_set_id
WHERE   s.database_name = DB_NAME()
Advertisements

IDENT_CURRENT,@@IDENTITY,SCOPE_IDENTITY()

Many Developer come across situation in which they fail figuring out difference in IDENT_CURRENT, @@IDENTITY, SCOPE_IDENTITY.

let’s understand What all 3 function returns.

  • IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
  • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
  • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
  • Let’s Execute following Script to create table in which we will Insert identity  and see what all 3 function returns.
--Block A:Create Table
CREATE TABLE Employee
(  EMP_id INT IDENTITY
PRIMARY KEY ,
Emp_Name VARCHAR(100) ,
Emp_City VARCHAR(100) ,
EMP_Designation VARCHAR(100)
)
--We storing history of employee progress as employee enters in organization
CREATE TABLE employee_History
(
HistoryID INT IDENTITY(100, 1) ,
Emp_ID INT ,
EMP_Name VARCHAR(100) ,
Designation VARCHAR(100) ,
Actionstatus VARCHAR(10) ,--Insert/Update/Delete
dtDate DATE
)
Go
--Block2:Create Trigger Block
CREATE TRIGGER TRI_Insert_Employee ON Employee
FOR INSERT
AS
INSERT  INTO dbo.employee_History
( Emp_ID ,
EMP_Name ,
Designation ,
Actionstatus ,
dtdate
)
SELECT  EMP_id ,
Emp_Name ,
EMP_Designation ,
'Insert' ,
GETDATE()
FROM    INSERTED
GO
CREATE TRIGGER TRI_Update_Employee ON Employee
FOR UPDATE
AS
INSERT  INTO dbo.employee_History
( Emp_ID ,
EMP_Name ,
Designation ,
Actionstatus ,
dtDate
)
SELECT  EMP_id ,
Emp_Name ,
EMP_Designation ,
'UPDATE' ,
GETDATE()
FROM    INSERTED
GO
CREATE TRIGGER TRI_DELETE_Employee ON Employee
FOR DELETE
AS
INSERT  INTO dbo.employee_History
( Emp_ID ,
EMP_Name ,
Designation ,
Actionstatus ,
dtDate
)
SELECT  EMP_id ,
Emp_Name ,
EMP_Designation ,
'DELETE' ,
GETDATE()
FROM    DELETED
GO

Let’s differ all differ all 3 function in easy way.

SCOPE_IDENTITY() =last identity ,any table,current session ,current scope.

@@IDENTITY           =last identity ,any table ,current session ,all scope.

IDENT_CURRENT    =last identity ,specific table ,any session,any scope.

let’s assume that diffrent developer have used diffrent function to get inserted identity.

Block 1:

INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES ( 'Johansan Keyas' ,
'New York' ,
'SQL Developer'
)
SELECT @@IDENTITY
/*Output: 100  */
SELECT SCOPE_IDENTITY()
/*Output: 1    */
SELECT IDENT_CURRENT('Employee')
/*Output: 1    */
  • When we executing Block 1 in this session current scope value 1  as identity inserted in Employee table so SCOPE_IDENTITY() returns value 1.
  • We have one insert trigger on employee table ,now it executes in this session but scope is  changed so @@IDENTITY returns value 100
  • IDENT_CURRENT returns the perfact value.
  • So in this Block 1 Scenario Developer who has not used @@IDENTITY  has winning situation.

Lets create trigger on employee table ,this scenario may never going possible,but let’s take  this example to understand when we may have a problem in getting right value inserted by developer.

Block  2:

CREATE TRIGGER TRI_Employee_Insert2 ON dbo.Employee
FOR INSERT
AS
INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES  ( 'Unknown',
'unkwnon',
'unknown'
)
GO

Lets Execute Block 3

Block 3:

INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES ( 'John Wills' ,
'New York' ,
'SQL Developer'
)
SELECT @@IDENTITY/*Output: 102*/
SELECT SCOPE_IDENTITY()/*Output: 2*/
SELECT IDENT_CURRENT('Employee')/*Output: 3*/
  • When we executing Block 3 in this session current scope value 2  as identity inserted in Employee table so SCOPE_IDENTITY() returns value 2.
  • We have two insert trigger on employee table ,now it executes in this session but scope is changed so @@IDENTITY returns value 102 which is inserted at last in current session.
  • IDENT_CURRENT returns value 3 which is inserted by trigger which is created in Block 3 Session.

Lets Disable trigger that insert value in employee_History on insert on Employee table

Block 4:

DISABLE TRIGGER TRI_Insert_Employee ON Employee
GO

Block 5:

INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES ( 'Krishnan Narayan' ,
'Banglore' ,
'SQL Developer'
)
SELECT @@IDENTITY/*5*/
SELECT SCOPE_IDENTITY()/*4*/
SELECT IDENT_CURRENT('Employee')/*5*/
  • When we executing Block 5 in this session current scope value 4  as identity inserted in Employee table so SCOPE_IDENTITY() returns value 4.
  • We have one insert trigger which has self insert on employee table ,now it executes in this session but scope is changed so @@IDENTITY returns value 5.
  • IDENT_CURRENT returns value 5 which is inserted by trigger which is created in Block 5 Session.

let’s take situation to see what happen when insert fails ,what this function retunrs the value.

we will provide long string so let’s reduce size of column.

Execute block 6

Block 6:

ALTER TABLE dbo.Employee
ALTER COLUMN Emp_City VARCHAR(10)
Go
DISABLE TRIGGER TRI_Employee_Insert2 ON Employee
Go

Before Running Following Block Please seee

Last identity inserted in Employee table it is 5 in my case.

Now Execute Block 7

Block 7:

INSERT INTO dbo.Employee
( Emp_Name ,
Emp_City ,
EMP_Designation
)
VALUES  ( 'Krishnan Narayan' ,
'Visakhapatnam' ,
'SQL Developer'
)
SELECT  @@IDENTITY/*5*/
SELECT  SCOPE_IDENTITY()/*4*/
SELECT  IDENT_CURRENT('Employee')/*6*/

We get following error after executing Block 7

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
  • Insertion is failed but we getting value in all 3 Identity Function and all 3 are different.

So Developer must while they are working with this function before using it into code.


SQL SERVER – Find Stored Procedure,Table,Function Related to Table

 

Some times we need to find Stored procedure,Tables,View,Function related to table.

Following are two script I have written  to find out Object Dependent on Table.

 

Script1:SQL Server 2005 and Later


SELECT DISTINCT   OBJECT_NAME(sd.referenced_major_id)TableName,
        OBJECT_NAME(sd.object_id)Ref_Object,
        CASE WHEN OBJECTPROPERTYEX(sd.object_id,N'ISTABLE')= 1
                     THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsTableFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsTableFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsScalarFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsTrigger')= 1
                     THEN'Trigger'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsView')= 1
                     THEN'View'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsUserTable')= 1
                     THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsProcedure')= 1
                     THEN'Procedure'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsIndexed')= 1
                     THEN'Index'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsForeignKey')= 1
                     THEN'ForeignKey'
        WHEN OBJECTPROPERTYEX(sd.object_id,N'IsPrimaryKey')= 1
                     THEN'PrimaryKey'
        END AS Ref_Object_Name
FROM    sys.sql_dependencies SD
        INNER JOIN sys.objects obj
                     ON obj.object_id=sd.referenced_major_id
WHERE   obj.is_ms_shipped= 0
        AND obj.type_desc='USER_TABLE'
        ORDER BY TableName,Ref_Object,Ref_Object_Name

Script 2:SQL Server 2008 and Later

SELECT  DISTINCT ISNULL(sd.referenced_schema_name+'.','')+ OBJECT_NAME(sd.referenced_id)TableName,
        OBJECT_NAME(sd.referencing_id)Ref_Object,
        CASE WHEN OBJECTPROPERTYEX(sd.referencing_id,N'ISTABLE')= 1
                     THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction')= 1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsScalarFunction')=1
                     THEN'Function'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTrigger')= 1
                     THEN'Trigger'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsView')= 1
                     THEN'View'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsUserTable')= 1
                     THEN'Table'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsProcedure')= 1
                     THEN'Procedure'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsIndexed')= 1
                     THEN'Index'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsForeignKey')= 1
                     THEN'ForeignKey'
        WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsPrimaryKey')= 1
                     THEN'PrimaryKey'
        END AS Ref_Object_Name
FROM    sys.sql_expression_dependencies SD
        INNER JOIN sys.objects obj
                     ON obj.object_id=sd.referenced_id
WHERE   obj.is_ms_shipped= 0
--AND  referenced_id=object_id('TableName') /*Where one can Replace table Name*/
        AND obj.type_desc='USER_TABLE'
        ORDER BY TableName,Ref_Object,Ref_Object_Name

sys.sql_dependencies View will be removed from future verison of SQL Server 
so it is better to use script2 Both Version are able to give table

Dependencyon other object.