SQL Server -Tail Log Backups

Tail Log Backup: Tail Log Back up is Back up of Tail of Log Just Before Before Restore operation of Database.

  • As it is Log Backup so we can take Tail Log Backup when recovery model Database either Full or Bulk Logged.

Like any log backup, a tail-log backup is taken by using the BACKUP LOG statement and it is  recommend that you take a tail-log backup in the following situations:

  • If the database is online and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:
    
    BACKUP LOG database_name TO <backup_device> WITH NORECOVERY
    
    -- If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:
    
    BACKUP LOG database_name TO <backup_device> WITH CONTINUE_AFTER_ERROR
    
    
  • Tail Lob Backup capture the tail of log even database is in offline mode,Database is damaged or missing data file.This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable.
  • If a tail-log backup has incomplete metadata, in the backupset table, has_incomplete_metadata is set to 1. Also, in the output of RESTORE HEADERONLY, HasIncompleteMetadata is set to 1.
  • If the metadata in a tail-log backup is incomplete, the backupfilegroup table will be missing most of the information about filegroups at the time of the tail-log backup. Most of the backupfilegroup table columns are NULL.

 

 

 

 

 

 

SQL Server-Collation

Collation: Collation is set of rules which Determines how data are sorted and compared.

collations can be specified at many levels. When we install an instance of SQL Server ,we can specify the default collation for that instance. Each time we create a database,we can specify the default collation used for the database. If we do not specify a collation, the default collation for the database is the default collation for the instance.

  • We can get list of Collation by Querying.
select  * from  ::fn_helpcollations()
Let’s take example of two collation to know how it differ in behavior in context of Data insertion.
1.SQL_Latin1_General_CP1_CS_AS (Case Senstive,Accent-Sensitive).
2.SQL_Latin1_General_CP1_CI_AI (Case Insenstive,Accent-Insensitive).
first we create different table for both collation.
  • Employee_CS for (case sensitive collation i.e. SQL_Latin1_General_CP1_CS_AS )
  • Employee_CI for (case insensitive collation i.e. SQL_Latin1_General_CP1_CI_AI )
let’s Create table  Employee_CS,we will define collation at column level in this table which is case  and accent sensitive.
--Creating table Employee_CS ,define collation at column level which is case sensitive and accent sensitive
CREATE TABLE Employee_CS
    (
      id INT IDENTITY(1, 1) ,
      NAME NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS PRIMARY KEY
    )
 We have made Column Name Primary key in Table Employee_CS.Let’s insert same name but with different case.

 --insert to value for name which is not same  for case sensitive,both are different
INSERT INTO dbo.Employee_CS
        ( NAME )
VALUES  ( 'Bill'),('BILL')
--Insertion will be completed without error even we have primary key on Name column cause of collation we define on it.

We got Successful insertion of the data in the table cause Bill and BILL is different while we are defining case sensitive collation type on Column.

  • Let’s Create  Another table Employee_CI and define collation at column level which is case sensitive.
--Creating table Employee_CI ,define collation at column level which is case insensitive ans accent insensitive

CREATE TABLE Employee_CI
    (
      id INT IDENTITY(1, 1) ,
      NAME NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI PRIMARY KEY
    )

Let’s insert Same name Bill and BILL into Name of Employee_CI.


--insert to value for name which is same for case Insensitive,both are different in term of case sensitive
INSERT INTO dbo.Employee_CI
 ( NAME )
VALUES ( 'Bill'),('BILL')
--Insertion steps throws error "Violation of PRIMARY KEY constraint"
  • As you run insertion step it throws error cause as collation is case insensitive.
  • Now Question is how we can compare column that having different collation?
  • Let’s insert Value in Table Employee_CI so we can compare Column Name with table Employee_CS
INSERT INTO dbo.Employee_CI
        ( NAME )
VALUES  ( 'Bill'),('BILLS')
  • When we write simple joining statement it throws error that collation conflict occur.
SELECT * FROM dbo.Employee_CI EI INNER JOIN dbo.Employee_CS ES ON EI.NAME = ES.NAME
--Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation.
  • We have to provide collation name where we are joining table based on column comparison as given below.
--You can give Default collation by stating DATABASE_DEFAULT.but you first got to know what collation current database have.
SELECT * FROM dbo.Employee_CI EI INNER JOIN dbo.Employee_CS ES
ON EI.NAME COLLATE SQL_Latin1_General_CP1_CI_AI= ES.NAME COLLATE SQL_Latin1_General_CP1_CI_AI
  • Here is code by which we can get collation at database level and server level.
SELECT DATABASEPROPERTYEX('sandbox','collation')--Gives Database level collation
SELECT SERVERPROPERTY('collation')--Gives Server Level collation

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()

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.