February 2020

February 26, 2020 , ,

 we can use the below script to check database backup restore percentage details and many more.



use master;

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) 
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], 
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], 
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], 
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], 
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, 
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) 
FROM sys.dm_exec_sql_text(sql_handle))) 
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')

Read more »

February 12, 2020
It is very important to take a database users and the respective privileges backup while you perform a database restore, as once the database restore complete we must replace the same access rights before so that application activities never disturb. 

to achieve this requirement we can use below script to take the specific database users and the roles. 

[code type="SQL"] Use <'database name here'> go SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + QuoteName(dp.name, char(39)) + ') CREATE USER ' + QuoteName(dp.name) + IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') + IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';' COLLATE DATABASE_DEFAULT FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid WHERE dp.type in ('G','U','S') go SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';' FROM sys.database_role_members r1 inner join sys.database_principals r2 on r1.member_principal_id = r2.principal_id inner join sys.database_principals r3 on r1.role_principal_id = r3.principal_id GO [/code]



Test code given below:

Use <'database name here'>
go
SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + 
QuoteName(dp.name, char(39)) + 
') CREATE USER ' + QuoteName(dp.name) + 
IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') + 
IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';' COLLATE DATABASE_DEFAULT 
FROM sys.database_principals dp 
LEFT JOIN sys.server_principals sp 
ON sp.sid = dp.sid 
WHERE dp.type in ('G','U','S') 
go
SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';' 
FROM sys.database_role_members r1 
inner join sys.database_principals r2 
on r1.member_principal_id = r2.principal_id 
inner join sys.database_principals r3 
on r1.role_principal_id = r3.principal_id 
GO


Read more »
Subscribe to: Posts (Atom)

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.