TSQL Script to get Backup details in SQL Server

One of my colleague was looking for a script to identify the backup details for one his databases. Here is a very simple script to identify database backup details.

Purposes:

1. To confirm all the backup jobs are running fine at the defined intervals
2. To confirm the necessary backup strategies are in place For ex: For FULL recovery model requires frequent log backups to avoid the space issues
3. To identify the backup startdate and finishdate for backups
4. To identify the device name to which backups are initiated
5. To identify who initiated the backups(username)

SCRIPT


SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   BkpSet.server_name,
   BkpSet.database_name, BkpSet.recovery_model,
   BkpSet.backup_start_date, BkpSet.backup_finish_date, 
   BkpSet.expiration_date, 
   CASE BkpSet.type  
       WHEN 'D' THEN 'Database(FULL)' 
       WHEN 'I' THEN 'Differential' 
       WHEN 'L' THEN 'Log'  
   END AS backup_type, BkpSet.backup_size,  
   BkpMF.logical_device_name,  BkpMF.physical_device_name,   
   BkpSet.name AS backupset_name, BkpSet.description, user_name
   FROM   msdb.dbo.backupmediafamily BkpMF
   INNER JOIN msdb.dbo.backupset BkpSet 
           ON BkpMF.media_set_id = BkpSet.media_set_id  
   WHERE  
   (CONVERT(datetime, BkpSet.backup_start_date, 102) >= GETDATE() - 7)  
   ORDER BY BkpSet.backup_finish_date desc

See Also:
Read more on SQL Server Job details:
https://latheeshnk.wordpress.com/2015/04/03/t-sql-script-get-sql-job-details-and-history-details/

Advertisements

Identify references of an object in SQL Server

Problem Statement:

Today, there was a requirement from one of my colleague “How to identify a function references in SQL Server?”.

Infact, He was looking for a script to identify the reference places(objects) where the function is being used in SQL Server. Please find the script below that we used to identify the references.

Script:

DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = N'split' --Give your function/proc name

--Table variable to hold the results from different databases
Declare @ResultTable Table(SourceSchema sysname, Sourceobject sysname, ReferencedDB sysname, ReferencedSchema sysname, ReferencedObject sysname)

Declare @MyQuery NVARCHAR(MAX) = N'
USE [?]
SELECT DISTINCT
	SourceSchema      = OBJECT_SCHEMA_NAME(sed.referencing_id)
	,SourceObject     = OBJECT_NAME(sed.referencing_id)
	,ReferencedDB     = ISNULL(sre.referenced_database_name, DB_NAME())
	,ReferencedSchema = ISNULL(sre.referenced_schema_name,
	OBJECT_SCHEMA_NAME(sed.referencing_id))
	,ReferencedObject = sre.referenced_entity_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ''.'' +       	                           OBJECT_NAME(sed.referencing_id), ''OBJECT'') sre
WHERE sed.referenced_entity_name like ''%' + @ObjectName + '%'' AND sre.referenced_entity_name like ''%' + @ObjectName + '%''';

Insert into @ResultTable
EXEC sp_MSforeachdb  @MyQuery

Select * From @ResultTable

The above script uses sp_MSforeachdb to execute the query in all databases available in the SQL Server.

Please note that sp_MSforeachdb is an undocumented procedure, Microsoft may change the functionality or definition of this Stored Procedure at any time.

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01
https://gallery.technet.microsoft.com/How-to-change-datacompressi-47dfce48