Restore Database From URL with Credential

Objective:

Restoring the database from Azure URL requires the below parameters.

RESTORE DATABASE [DBNAME] FROM  URL = N'https://AzureStorageAccountName.blob.core.windows.net/BlobContainerName/DBNAME.bak' WITH  FILE = 1, 

CREDENTIAL = 'BackupAzureCredential',

MOVE N'DBNAME_PrimaryData' TO N'V:\User-Data\DBNAME.mdf', 

MOVE N'DBNAME_Log' TO N'U:\User-Logs\DBNAME_log.ldf',  NOUNLOAD,  STATS = 5

Find the latest Managed SQL Server Backup STATUS

USE msdb 

GO 

SELECT 

sbf.[database_name] AS [Database Name] 

--,sbf.backup_path AS [Backup Destination and File] 

--,[Backup Type] = 

--CASE sbf.backup_type 

--WHEN 1 THEN 'FULL' 

--WHEN 2 THEN 'LOG' 

--END 

--,[Backup Status] = 

--CASE sbf.[status] 

--WHEN 'A' THEN 'Available' 

--WHEN 'B' THEN 'Copy In Progress' 

--WHEN 'C' THEN 'Corrupted' 

--WHEN 'D' THEN 'Deleted' 

--WHEN 'F' THEN 'Copy Failed' 

--WHEN 'U' THEN 'Unknown' 

--END 

--,sbf.first_lsn AS [First LSN] 

--,sbf.last_lsn AS [Last LSN] 

--,sbf.backup_start_date AS [Backup Start Time] 

--,sbf.backup_finish_date AS [Backup Completion Time] 

--,sbf.expiration_date AS [Backup Expiry Date/Time] 

--,sdb.[Name] AS DatabaseName

,COALESCE(CONVERT(VARCHAR(12), MAX(sbf.backup_finish_date), 101),'-') AS LastBackUpTime

FROM

sys.sysdatabases sdb

LEFT OUTER JOIN smart_backup_files sbf  ON sbf.database_name = sdb.name

where backup_type in (1,2) 
-- 1 and 2 is FULL and Log backup--and cast(backup_finish_date as varchar(11))= cast(getdate() as varchar(11))

GROUP BY sbf.database_name

order by LastBackUpTime desc

Disabling SSRS Reports Subscription

Step I:


The below query can retrieve the ScheduleID


Use ReportServerDatabase
go
select cl.ItemID,cl.Path,cl.Name,rs.ScheduleID from Catalog cl inner join ReportSchedule rs on cl.ItemID = rs.ReportID


where cl.path like '/LearnerReports/%'  -- This is the path where you have the SSRS.


Step II:


Once Recevied the scheduleID, concatenate with excel or loop to get all the SQL Server Agent jobs for SSRS to be disabled.


Use msdb
go
exec sp_update_job @job_name = 'jobname',@enabled = 0 -- by default, the jobname is equal to the scheduleID.


Example after Loop or Concatenate:




exec sp_update_job @job_name = '9B7E93A0-F5A-4GBF3-8B8E-253EC2E27819',@enabled =0
exec sp_update_job @job_name = '8B7E97A0-F8A-4GBG3-8B8E-259EC2E27200',@enabled =0

SSRS Last Run Report Name - Finding

Objective:


To find the last run report on SSRS.


Query to Execute on Report Database:


SELECT TOP (1) PERCENT
ReportPath,
TimeStart,
TimeEnd,
UserName,
RequestType,
Format,
TimeDataRetrieval,
TimeProcessing,
TimeRendering,
[Source]


FROM

dbo.ExecutionLog2


WHERE
(ReportPath = N'/Report Project1/Report1') --- Path of the report


ORDER BY
TimeStart DESC

Blocking Finding Query on SQL Server

Objective:


Blocking information for SQL Server can be identified using the below query.


Query:




SELECT s.session_id

    ,r.STATUS

    ,r.blocking_session_id AS 'blocked_by'

    ,r.wait_type

    ,r.wait_resource

    ,CONVERT(VARCHAR, DATEADD(ms, r.wait_time, 0), 8) AS 'wait_time'

    ,r.cpu_time

    ,r.logical_reads

    ,r.reads

    ,r.writes

    ,CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) AS 'elapsed_time'

    ,CAST((

            '<?query --  ' + CHAR(13) + CHAR(13) + Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (

                    (

                        CASE r.statement_end_offset

                            WHEN - 1

                                THEN Datalength(st.TEXT)

                            ELSE r.statement_end_offset

                            END - r.statement_start_offset

                        ) / 2

                    ) + 1) + CHAR(13) + CHAR(13) + '--?>'

            ) AS XML) AS 'query_text'

    ,COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS 'stored_proc'

    --,qp.query_plan AS 'xml_plan'  -- uncomment (1) if you want to see plan

    ,r.command

    ,s.login_name

    ,s.host_name

    ,s.program_name

    ,s.host_process_id

    ,s.last_request_end_time

    ,s.login_time

    ,r.open_transaction_count

FROM sys.dm_exec_sessions AS s

INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st




--OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp -- uncomment (2) if you want to see plan
WHERE r.wait_type NOT LIKE 'SP_SERVER_DIAGNOSTICS%'

    OR r.session_id != @@SPID

ORDER BY r.cpu_time DESC

    ,r.STATUS

    ,r.blocking_session_id

    ,s.session_id