SIVA SQL Server: 2013

Friday, October 18, 2013

Query to get the latest FULL backup details

SELECT
cast ((backup_finish_date - backup_start_date) AS TIME(0)) as DURATION,
SERVER_NAME,b.DATABASE_NAME,[TYPE],backup_set_id,
b.backup_start_date,b.backup_finish_date,
Round(b.backup_size/(1024*1024),2) as Size_n_MB,
Physical_device_name
FROM msdb.dbo.backupset b,
( select a.DATABASE_NAME, MAX(a.backup_set_id) id
from msdb.dbo.backupset a
WHERE a.TYPE = 'D' and
a.DATABASE_NAME = DATABASE_NAME
group by a.DATABASE_NAME ) cc,
msdb.dbo.backupmediafamily f
WHERE TYPE = 'D'
AND b.backup_set_id = cc.id
AND b.media_set_id = f.media_set_id

Find time difference between two dates

SELECT
b.backup_finish_date,
b.backup_start_date,
CAST ((b.backup_finish_date - b.backup_start_date) AS TIME(0))
FROM backupset b
WHERE convert(varchar(12),b.backup_finish_date,101) = '10/13/2013'

Wednesday, September 25, 2013

Update job notification

Following Query will update the Existing job name starts with ‘WIN%’ as on complete to notify the email operator 'SQLDBASupport'

select
'EXEC msdb.dbo.sp_update_job @job_name=N'+CHAR(39)+name+CHAR(39)
+' ,
        @notify_level_email=3,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @notify_email_operator_name=N'+char(39)+'SQLDBASupport'+char(39)
from msdb.dbo.sysjobs
where name like 'WIN%'
and notify_email_operator_id =0

Thursday, August 15, 2013

SQL Server: Query to check if given column exists

select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        d.[max_length]      'Max Length',
        d.[precision]       'Precision',
        c.[is_identity]     'Is Id',
        c.[is_nullable]     'Is Nullable',
        c.[is_computed]     'Is Computed',
        d.[is_user_defined] 'Is UserDefined',
        t.[modify_date]     'Date Modified',
        t.[create_date]     'Date created'
from        sys.schemas s
inner join  sys.tables  t
on s.schema_id = t.schema_id
inner join  sys.columns c
on t.object_id = c.object_id
inner join  sys.types   d
on c.user_type_id = d.user_type_id
where c.name like 'Person%'

Tuesday, April 30, 2013

How to: search for a word or column name across the Procedure, Function, Trigger, View

DECLARE @SearchPhrase nvarchar(1000)

SET @SearchPhrase = N'backup' -- Place your search string

SELECT
DISTINCT sysobjects.name AS [Object Name],
CASE
WHEN sysobjects.xtype = 'P' THEN 'Stored Proc'
WHEN sysobjects.xtype = 'TF' THEN 'Function'
WHEN sysobjects.xtype = 'TR' THEN 'Trigger'
WHEN sysobjects.xtype = 'V' THEN 'View'
END as [Object Type],
(SELECT ParentTable.[Name]
FROM sysobjects ParentTable
WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable
FROM sysobjects,
syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR', 'V')
--AND sysobjects.category = 0 --- To restrict only user defined object
AND CHARINDEX(@SearchPhrase, syscomments.text) > 0

How to: find when the database last got restored?

   1:  SELECT destination_database_name,max(restore_date) as restore_date

   2:  FROM msdb.dbo.restorehistory

   3:  WHERE destination_database_name = 'testDB’  ---- Place your database name here

   4:  GROUP BY destination_database_name

The result will look like;

destination_database_name restore_date

testDB 2013-04-29 11:49:19.940

SQL 2008 Management Studio does not allow to save changes to tables

In SQL Server 2008 Management Studio,

We can not do

•Change the Allow Nulls setting for a column

•Reorder the columns

•Change the column data type

•Add a new column

How to fix / Resolution:

•Go to Tools -> Options -> Designers -> Tables and Designers

•Uncheck the Prevent Saving Changes that require table re-creation

Wednesday, March 20, 2013

SQL Server Contained Database Authentication process flow

Source: http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx

SIVA SQL Server