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