Posts

Showing posts from September, 2021

Restore-DbaDatabase,dbatools

Image
We can use below dbatools to restore database. Export-DbaUser will export all the respective database users to a spcific file. If you want all the user script need to add single file then user -FilePath parameter.If you want indivual files need to created user -Path parameter. cls Export - DbaUser ` - SqlInstance InstanceNameHere ` - Database DatabaseNameHere ` - FilePath C : \ Temp \ Users_DatbaseNameHere_InstanceNameHere. sql ` - Verbose In the below command -Path parameter you no need pass .BAK file it will automatically read file. -WithReplace option will overwrite the existing database. Restore - DbaDatabase ` - SqlInstance InstanceNameHere ` - DatabaseName DatabaseNameHere ` - Path H: \ TodayRestore ` - DestinationDataDirectory "F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\DatabaseName_Data.mdf" ` - DestinationLogDirectory "G:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\DatbaseName_Log.ldf" ` - Wit...

LSN of Transaction Log Backups.

The below query will tell us the history of Transaction Log Backups. SELECT DISTINCT s.database_name, s.first_lsn, s.last_lsn,                         s.database_backup_lsn, s.backup_start_date, s.backup_finish_date,                         s. type , s.is_snapshot,y.device_type, y.logical_device_name, y.physical_device_name FROM msdb..backupset AS s INNER JOIN msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id where database_name = 'database_name'

Which user has SHOWPLAN permissions at database level.

The below query will tells us the which user has SHOWPLAN permission on the database level. DECLARE @ Table TABLE (ServerName varchar ( 50 ),DatabaseName varchar ( 50 ), [Name] varchar ( 50 ),PermissionName varchar ( 50 ),StateDesc varchar ( 50 )) INSERT INTO @ Table (ServerName,DatabaseName,[Name],PermissionName,StateDesc) EXEC sp_MSforeachdb 'USE ? SELECT ServerName=@@ServerName, DatabaseName=db_name(), p.name, perm.permission_name,perm.STATE_DESC FROM sys.database_permissions perm INNER JOIN sys.database_principals p ON perm.grantee_principal_id = p.principal_id WHERE perm.permission_name = ''SHOWPLAN'' AND [Name] in(''User1'',''User2'', ''User3'',''User4j'');' SELECT * FROM @ Table order by [Name] DESC