SQL Server Backup Status (Full & Transaction)

SQL Server Database Backup Status

Using below scripts we can find if a disk level backup has happend for a DB in last 7 days or not and we can check the status Transaction Log backup and we will get an entry if the Transaction Log is more than 10 GB.

For SQL Server 2000

select distinct @@servername [Server Name],bs.database_name Bkup_DB_Name,db.name [DB_Name],DATABASEPROPERTYEX(db.name,'Recovery') recovery_model_desc,DATABASEPROPERTYEX(db.name,'Recovery') log_reuse_wait_desc, case when bs.backup_finish_date is Null then 'No Latest Full Backup' when bs.backup_finish_date>=(getdate()-7) then 'Latest Backup' when bs.backup_finish_date<(getdate()-7) then 'Not Latest Backup' else 'Check Last Backup of the Database' end [Backup Status], case when bs.database_name is NULL then 'Take a full backup of '+db.name+' Database and check in Maintenance Plan' else 'We have a full backup, check latest or not' end Message from (msdb..backupmediafamily bm join msdb.dbo.backupset bs on bs.media_set_id=bm.media_set_id and device_type=2 and backup_finish_date>=(getdate()-7) ) right outer join master.dbo.sysdatabases db on bs.database_name=db.name where db.name<>'tempdb' and DATABASEPROPERTYEX(db.name,'status')='ONLINE' and (bs.database_name is null) union select @@servername [Server Name],instance_name Perf_DB_Name,db.name [DB_Name],DATABASEPROPERTYEX(instance_name,'Recovery') recovery_model_desc,cntr_value, case when cntr_value>=80 then 'Transaction Log usage is more than 80%' when cntr_value<79 then 'Transaction Log usage is less than 80%' else 'check' end [Backup sattus], case when cntr_value>=80 then 'Take a Transaction Log Backup of '+db.name+' Database and check in Maintenance Plan' else 'Check Database' end [Message] from master.dbo.sysperfinfo sp join master.dbo.sysdatabases db on db.name=sp.instance_name and COUNTER_NAME LIKE '%Percent Log Used%' AND INSTANCE_NAME NOT IN ('_Total','mssqlsystemresource') and cntr_value>=80 union select @@servername [Server Name],instance_name Perf_DB_Name,db.name [DB_Name],DATABASEPROPERTYEX(instance_name,'Recovery') recovery_model_desc,cntr_value/1024/1024 Counter_value, case when (cntr_value/1024/1024)>=10 then 'Transaction Log usage is more than 10 GB' when (cntr_value/1024/1024)<10 then 'Transaction Log usage is less than 10 GB' else 'check Database' end [Backup sattus], case when (cntr_value/1024/1024)>=10 then 'Take a Transaction Log Backup of '+db.name+' Database and check in Maintenance Plan' else 'Check Database' end [Message] from master.dbo.sysperfinfo sp join master.dbo.sysdatabases db on db.name=sp.instance_name and COUNTER_NAME LIKE '%Log File(s) Size (KB) %' AND INSTANCE_NAME NOT IN ('_Total','mssqlsystemresource') and (cntr_value/1024/1024)>=10

For SQL Server 2005 and 2008

select distinct @@servername [Server Name],bs.database_name Bkup_DB_Name,db.name [DB_Name],db.recovery_model_desc,db.log_reuse_wait_desc, case when bs.backup_finish_date is Null then 'No Latest Full Backup' when bs.backup_finish_date>=(getdate()-7) then 'Latest Backup' when bs.backup_finish_date<(getdate()-7) then 'Not Latest Backup' else 'Check Last Backup of the Database' end [Backup Status], case when bs.database_name is NULL then 'Take a full backup of '+db.name+' Database and check in Maintenance Plan' else 'We have a full backup, check latest or not' end Message from (msdb..backupmediafamily bm join msdb.dbo.backupset bs on bs.media_set_id=bm.media_set_id and device_type=2 and backup_finish_date>=(getdate()-7) ) right outer join master.sys.databases db on bs.database_name=db.name where db.name<>'tempdb' and db.state_desc='ONLINE' and (bs.database_name is null) union select @@servername,name,name,recovery_model_desc,log_reuse_wait_desc,case when log_reuse_wait_desc='LOG_BACKUP' then 'Trans Log getting Full' else log_reuse_wait_desc end,case when log_reuse_wait_desc='LOG_BACKUP' then 'Take a Transaction Log Backup of '+name+' Database and check in Maintenance Plan' else 'Check Database' end Message from master.sys.databases where log_reuse_wait_desc='LOG_BACKUP'

Finding and dropping Orphan Users in a DB

Below script can be used to find and drop the orphan users in a Database

 

 

Below script will list all the Orphan Users in a DB, we can select the output of [Script_To_Drop_User] column of the table variable and execute the same to Drop the user.

 

 -- Change the DB as per your DB Name

 

Use [PubDB]
declare @tbl_orpusers table (name varchar(260),[sid] varchar(100))

insert @tbl_orpusers
exec sp_change_users_login 'report'

select name [Orphan User],'DROP USER ['+name+']' [Script_To_Drop_User]from @tbl_orpusers



Checking the Orphan Users:

 

 

 

 

 

 

 

Below Link can be used to work on Orphan users in a DB,

 

http://msdn.microsoft.com/en-IN/library/ms175475.aspx

http://msdn.microsoft.com/en-IN/library/ms174378.aspx

 

 

 

Script to Disable Multiple Jobs in a SQL Server

Using below script we can generate the SP scripts to disable multiple jobs on a SQL Server

Method 1:
Below script will list all the Active Jobs configured on the SQL Server, we can select the output of [Script_To_Disable_Job] column of the jobs of our interest (which we wanted to disable) and execute the same to disable the job.

 

select

'EXEC msdb.dbo.sp_update_job @job_name='''+name+''''+', @enabled=0' [Script_To_Disable_Job]
,name,[enabled]
from msdb.dbo.sysjobs
where [enabled]=1

Below screen shots shows how we disabled few jobs using above script.





Method 2:

We can disable all the Active Jobs on a SQL Server by executing the below query on that server.

Note: Cross verify before executing the script as it disables all the Active Jobs in single shot.
 

use msdb

set nocount on

declare @maxjobcnt int,@jobname2dis varchar(260),@wcnt int=1
select @maxjobcnt=count(*) from msdb.dbo.sysjobs
where [enabled]=1

 

declare @tbl_disablejobs table(cnt int identity(1,1),

name varchar(260),[enabled] tinyint)

insert @tbl_disablejobs (name,[enabled])
select name,[enabled]
from msdb.dbo.sysjobs
where [enabled]=1
--select * from @tbl_disablejobs

 

while (@wcnt<=@maxjobcnt)

Begin

select @jobname2dis=name from @tbl_disablejobs
where cnt=@wcnt

--select @jobname2dis

EXEC msdb.dbo.sp_update_job @job_name=@jobname2dis, @enabled=0
Print '['+@jobname2dis+'] Job is Disabled'
set @wcnt=@wcnt+1
End

set nocount off

 

Get Mail Alert when SQL Server Restarts

Using below query we can configure a alert mail whenever a SQL Server gets restarted

--- Checking startup stored procedures configuration status

SELECT VALUE, VALUE_IN_USE, DESCRIPTION

FROM SYS.CONFIGURATIONS

WHERE NAME = 'scan for startup procs'

GO

--- Create table in one of the User Database

CREATE TABLE ECSDBAMonitor.dbo.server_restart_info

(ServerName varchar(500),ReportTime datetime,LogDate datetime,LastText varchar(1000))

--- Create Procedure in Master Database

create PROCEDURE usp_server_restart_log

As

Begin

SET NOCOUNT ON

Create table #errorlog (logdate datetime,processinfo varchar(500),lasttext varchar(1000))

declare @startdate datetime,@enddate datetime

set @enddate=getdate()

set @startdate=dateadd(mi,-90,getdate())

--select @enddate,@startdate

insert #errorlog

Exec xp_readerrorlog 1,1,NULL,NULL,@startdate,@enddate,'desc'

declare @logdate datetime,@lasttext varchar(1000)

select top 1 @logdate=logdate,@lasttext=lasttext from #errorlog

--select @logdate,@lasttext

delete  ECSDBAMonitor.dbo.server_restart_info  where ReportTime<(getdate()-180)

insert into ECSDBAMonitor.dbo.server_restart_info values

(@@servername,getdate(),@logdate,@lasttext)

Drop table #errorlog

End

Go

--- Configuring startup stored procedures for the server

EXEC sp_procoption @ProcName = 'usp_server_restart_log'

    , @OptionName =  'startup'

    , @OptionValue = 'on';

--- Checking startup stored procedures configuration status

SELECT VALUE, VALUE_IN_USE, DESCRIPTION

FROM SYS.CONFIGURATIONS

WHERE NAME = 'scan for startup procs'

GO

--- Creating a Trigger on User Table with which we would be getting an alert mail when SQl Server

--- Restart and adds a row to user table. Chnage the Mail Id's, Profile Information

--- as per the environment

create TRIGGER tr_server_restart_info_mail

ON server_restart_info

AFTER INSERT

AS

declare @servername varchar(500),@reporttime datetime,@logdate datetime,@lasttext varchar(1000)

select @servername=ServerName,@reporttime=ReportTime

,@logdate=LogDate,@lasttext=LastText

     from inserted

declare @body varchar(2000)

select @body=N'<H1><font size="2" face="cambria">Hi Team,<br><br>'

select @body=@body +N' Below SQL Server was restarted at below mentioned time, please check if the server

was restarted unexpectedly. Please ignore the mail if the server was restarted for

maintenance activity<br><br></H1>'

Select @body = @body+

    N'<H1><font size="2" face="cambria">SQL Server Restart Information</font></H1>' +

    N'<table border="1">' +

    N'<tr><th><font size="2" face="cambria">ServerName</th><th>

<font size="2" face="cambria">ReportTime</th><th>

<font size="2"    face="cambria">LogDate</th><th>

<font size="2"   face="cambria">LastText</th>'

 select @body = @body + '<tr><th><font size="2" color="forestgreen" face="cambria">'+isnull(convert(varchar(500),@servername),' ')+

'</font></th><th><font size="2"  color="forestgreen" face="cambria">'+isnull(convert(varchar(500),@reporttime),' ')+

'</font></th><th><font size="2"  color="forestgreen" face="cambria">'+isnull(convert(varchar(500),@logdate),' ')+

'</font></th><th><font size="2"  color="forestgreen" face="cambria">'+isnull(convert(varchar(1000),@lasttext),' ')+

'</font></th>'

 select @body = @body + '</tr></table>'

declare @subject varchar(500)

set @subject='SQL Server Restart Info of '+@servername

 EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'pawankalyan@power.com',

    --@copy_recipients=' pawankalyan@power.com',

    @subject =@subject,

    @body =@body,

    @profile_name='Mail Alerts',

    @body_format = 'HTML' ;

Go

Users and permissions in Database

Below Script is used to find the Users in Database and their permissions

SQL Server 2000

use [master]

select db_name(0)AS DBName, a.name, CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'

    WHEN a.issqlrole=1 THEN 'Database Role'

    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'

from  master..syslogins sl join dbo.sysusers a  on a.sid=sl.sid   LEFT OUTER JOIN dbo.sysmembers b ON a.uid=b.memberuid where a.altuid<>1 and a.uid not in (1,2) AND 'DBName' NOT IN ('master','msdb','model','tempdb') ORDER BY a.Name

SQL Server 2008

use [master]

Select db_name(0) As DBName,a.name 'User_Name',

CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'

    WHEN a.issqlrole=1 THEN 'Database Role'

    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'

from master..syslogins sl join sysusers a on sl.sid=a.sid

LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid

--where a.altuid<>1

SQL Server 2008

use master

Select db_name(0) As DBName,a.name 'User_Name',

CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'

    WHEN a.issqlrole=1 THEN 'Database Role'

    ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID',

    default_schema_name

from master..syslogins sl join sysusers a on sl.sid=a.sid

LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid

left join sys.database_principals dp on dp.principal_id=a.uid

--where a.altuid<>1

Script to find User Permissions on all DB's in a SQL Server 

create table #DBuserperm (DBName varchar(100),User_Name varchar(100),Login_Type varchar(100)

,Associated_Role varchar(100),LoginID varchar(100),Default_Schema_Name varchar(100),)

declare @dbname varchar(100)

declare @sqlQuery nvarchar(4000)

declare dbcursor CURSOR for

select name from sys.databases

where name not in ('master','model','msdb','tempdb')

OPEN dbcursor

FETCH NEXT FROM dbcursor

into @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

set @sqlQuery='Use ['+@dbname+']'

      set @sqlQuery =@sqlQuery+ 'Select db_name(0) As DBName,a.name '''+'User_Name'+''',

CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN '''+'Windows Group'+'''

    WHEN a.isntgroup =0 AND a.isntuser=1 THEN '''+'Windows Login'+'''

    WHEN a.issqlrole=1 THEN '''+'Database Role'+'''

    ELSE '''+'SQL Login'+''' END AS '''+'Login Type'+''',USER_NAME(b.groupuid) AS '''+'AssociatedRole'+''' ,

    sl.name '''+'Login_ID'+''',

    default_schema_name

from master..syslogins sl join sysusers a on sl.sid=a.sid

LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid

left join sys.database_principals dp on dp.principal_id=a.uid'

insert into #DBuserperm

      exec sp_executesql @sqlQuery 

FETCH NEXT FROM dbcursor

into @dbname

END

select * from #DBuserperm

drop table #DBuserperm

CLOSE dbcursor

Deallocate dbcursor