-> Below query can be used to find if a database instance uses Enterprise features or not.

set nocount on
Declare @name varchar(255)
declare @sql varchar(max)
create table #Features(
ServerName varchar(29),
DatabaseName varchar(100),
feature_name varchar(100),
feature_id int)
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
OPEN db_cursor

DECLARE DatabaseBackup_cursor CURSOR FOR
select name from sys.databases where state=0
OPEN DatabaseBackup_cursor
FETCH NEXT FROM DatabaseBackup_cursor into @name
WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'SELECT @@servername,''['+@name+']'' as DatabaseName,*
FROM ['+@name+'].sys.dm_db_persisted_sku_features
ORDER BY feature_name;'

insert into #Features
exec (@sql)
FETCH NEXT FROM DatabaseBackup_cursor into @name
END
CLOSE DatabaseBackup_cursor
DEALLOCATE DatabaseBackup_cursor
CLOSE db_cursor
DEALLOCATE db_cursor
select * from #Features
drop table #Features

-> I use this query predominantly to see if existing Enterprise edition SQL Server instances use Enterprise features or not. This allows me to see if a SQL Server instance can be moved to Standard edition from Enterprise if there are no enterprise features used.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> TSQL to monitor Backup and Restore operation,

SELECT start_time, percent_complete, DATEADD (ss,DATEDIFF
(ss,start_time,getdate()) / percent_complete * 100,start_time) AS Expected_end_time, st.text as SQL, command as Current_Command
,session_id FROM sys.dm_exec_requests er CROSS APPLY
sys.dm_exec_sql_text(er.sql_handle) st
WHERE percent_complete > 0

-> TSQL to find backup history for a database.

select b.database_name, b.type, bf.physical_device_name, b.backup_start_date,
b.backup_finish_date, b.backup_size/1024/1024/1024 as Backup_size_in_GB
from msdb..backupset b
left outer join msdb..backupmediafamily bf on bf.media_set_id = b.media_set_id
where b.type in('D') and b.database_name = 'JBDB'
order by b.backup_finish_date desc

-> TSQL to find restore history for a database.

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bs].[server_name] as [source_server_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
,[bs].[type] as BackupType
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
where [rs].[destination_database_name]='JBDB'
ORDER BY
[rs].[restore_date] DESC

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.