TSQL to find users and their access on a given database

TSQL to find users and their access on a given database

--
SELECT      dp1.name AS DatabaseRoleName,
            COALESCE(DP2.name, 'No members') AS DatabaseUserName
FROM        sys.database_principals AS dp1
LEFT  JOIN  sys.database_role_members AS drm
      ON    drm.role_principal_id = dp1.principal_id
LEFT  JOIN  sys.database_principals AS dp2
      ON    dp2.principal_id = drm.member_principal_id
WHERE       dp1.type = 'R'
ORDER BY    dp1.name 



SELECT pr.principal_id, pr.name, pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc,   
    pe.permission_name, s.name + '.' + o.name AS ObjectName  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id  
JOIN sys.objects AS o  
    ON pe.major_id = o.object_id  
JOIN sys.schemas AS s  
    ON o.schema_id = s.schema_id;   

--
--
Posted in Script, SQL, SQL Query, Uncategorized | Tagged , , , | Leave a comment

TSQL – Restore database,store filelistonly, restore on litespeed server

--

-- Show the lsit of all database files from backup files
RESTORE FILELISTONLY FROM DISK =  N'E:\FilePaath\File.BAK', WITH FILE = 1
GO

-- Restoring a Litespeed backup 
exec master.dbo.xp_restore_database @database = N'New_DatabaseName' ,
@filename = N'E:\FilePaath\File.LSBAK',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''DB_dat1'' TO N''L:\PATH\mydatafile.ndf''',
@with = N'MOVE N''DB_Log1'' TO N''L:\PATH\mylogfile.ndf''',
@affinity = 0,
@logging = 0

-- Restoring a Litespeed backup 
RESTORE DATABASE 'New_DatabaseName' FROM DISK = N'E:\FilePaath\File.LSBAK'
WITH MOVE 'DB_dat1' TO 'L:\PATH\mydatafile.ndf',
MOVE 'AdventureWorks_Data' TO 'c:\mssql\data\yukon\AdventureWorksCopy_Data.mdf', 
MOVE 'AdventureWorks_Log' TO 'c:\mssql\log\yukon\AdventureWorksCopy_Log.ldf', 
,RECOVERY, REPLACE
--
--
Posted in SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

TSQL to find size of all databases on a Server – Azure

--
-- DatabZse Name and Database Size

select
       @@serverName as ServerName,
    db.name as DatabaseName,
       (select top 1 s.storage_in_megabytes from sys.resource_stats s where s.database_name = db.name order by s.start_time desc) as DatabaseMB
from
       sys.databases db 
order by db.name 


-- DB Size and additional details including pool name 
select
       @@serverName as ServerName,
    db.name as DatabaseName, db.create_date, db.state_desc, 
   (select top 1 s.storage_in_megabytes from sys.resource_stats s where s.database_name = db.name order by s.start_time desc) as DatabaseMB
,do.edition, do.service_objective, do.elastic_pool_name
from
       sys.databases db JOIN sys.database_service_objectives do on db.database_id = do.database_id
order by db.name 
--
--
Posted in Azure, Script, SQL, SQL Query, SQL Server, Uncategorized | Tagged , , , , | Leave a comment

Find In-Memory table in a given database

Note: In Azure if we use premium database functions we can not downgrade those databases into standard pricing tier. We need to remove premium database features before downgrading to Standard tier.

I had to create multiple database copies to reproduce an issue where I don;t need premium database features. I end up deleting In-Memory tables before downgrading.

FYI: I was able to downgrade from P1 to S0 reduce the cost from 435$ a month to 15$ a month on X number of databases.

In-Memory tables is one of the premium database feature.

--
--
select schema_name(schema_id)+'.'+OBJECT_NAME(mt.object_id) TableName from sys.dm_db_xtp_table_memory_stats mt
join sys.tables st on st.object_id = mt.Object_id 
where mt.object_id > 0
--
Posted in SQL, SQL Query, SQL Server, Uncategorized | Tagged , , , , , , , , , | Leave a comment

TSQL to find Databases, corresponding elastic pool names and DB edition – Azure

-- TSQL to find Databases, corresponding elastic pool names and DB edition

SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName,
       dso.edition
FROM
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
ORDER BY
       d.name, dso.elastic_pool_name
--
--

Note: User has no option to change the edition/Service tier of master database. This is expected to change based on the number of databases on a given Azure SQL Server. But Microsoft has the ability to change to address performance on a given server.

Posted in SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

Create SQL Script to restore Transaction logs

I was part of a Major incident this week and we had to restore days worth of transaction logs on multiple databases.

Created SQL Query which generates the restore script

-- This Query generates SQL script to restore logs from one server to other during mirroring or always on or any other HA configuration

DECLARE @myStartTime datetime
SET @myStartTime = dateadd(dd,-2,getdate())

SELECT top 100 'restore LOG [' + database_name + '] FROM DISK =  ''\\' + convert(varchar(80),SERVERPROPERTY('MachineName')) + '\'+  replace(physical_device_name,':','$') + ''' with NORECOVERY' as Query
,backup_finish_date
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
where database_name in ('myDatabaseName')
and type = 'L' -- Transaction log backups 
and backup_finish_date > @myStartTime
ORDER BY backup_finish_date

--
--
Posted in Script, SQL, SQL Query, SQL Server, Uncategorized | Tagged , , , , , , | Leave a comment

TSQL to find database count in each Elastic pool and its storage utilization – Azure

-- TSQL to find database count in each Elastic pool and its storage utilization.
-- This needs to be executed against SQL Serve on Master Database 
; WITH CTE1 AS (
select
       isnull(d2.elastic_pool_name,'Not in Pool') as PoolName, count(*) as DatabaseCount 
from
       sys.databases d1 inner join sys.database_service_objectives d2 on d1.database_id = d2.database_id
	   where d1.name <> 'master'
group by isnull(d2.elastic_pool_name,'Not in Pool') )
, CTE2 as (
select elastic_pool_name, avg_storage_percent, elastic_pool_storage_limit_mb
,ROW_NUMBER() OVER (PARTITION BY elastic_pool_name order by end_time desc) as myrow
from sys.elastic_pool_resource_stats WHERE end_time > dateadd(hh,-1,getdate()) ) 
select @@Servername as ServerName, CTE1.PoolName, CTE1.DatabaseCount, CTE2.avg_storage_percent, CTE2.elastic_pool_storage_limit_mb	 from CTE1 join CTE2 on CTE1.PoolName = CTE2.elastic_pool_name
where cte2.myrow = 1 
-- 

Posted in Azure, Script, SQL, SQL Server, Uncategorized | Tagged , , , , | Leave a comment