sql server

Find out when last bakcup was restored in SQL Server DB

Posted on Updated on

Sometime we may need to find out which date some perticular database was restored and by whom? Use following query to find out this information.

WITH LastRestores AS ( SELECT DatabaseName = [d].[name] , [d].[create_date] , [d].[compatibility_level] , [d].[collation_name] , r.*, RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC) FROM master.sys.databases d LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name ) SELECT * FROM [LastRestores] WHERE [RowNum] = 1

SQL Server DBA Exam 70-764

Posted on Updated on

  • Technology: SQL Server 2016/2017/2019
  • Credit toward certification: MCSA
  • Microsoft Site Link: Exam 70-764

Configure data access and auditing (20–25%)

  • Configure encryption
    • Implement cell-level encryption
    • Implement Always Encrypted
    • Implement backup encryption
    • Configure transparent data encryption
    • Configure encryption for connections
    • Troubleshoot encryption errors
  • Configure data access and permissions
    • Manage database object permissions
    • Create and maintain users
    • Create and maintain custom roles
    • Configure user options for Azure SQL Database
    • Configure row-level security
    • Configure dynamic data masking
  • Configure auditing
    • Configure an audit on SQL Server
    • Query the SQL Server audit log
    • Manage a SQL Server audit
    • Configure an Azure SQL Database audit
    • Analyze audit logs and reports from Azure SQL Database

Manage backup and restore of databases (20–25%)

  • Develop a backup strategy
    • Back up very large databases
    • Configure alerting for failed backups
    • Back up databases to Azure
    • Manage transaction log backups,
    • Configure database recovery models
    • Configure backup automation
  • Restore databases
    • Perform piecemeal restores
    • Perform page recovery
    • Perform point-in-time recovery
    • Restore file groups
    • Develop a plan to automate and test restores
  • Manage database integrity
    • Implement database consistency checks
    • Identify database corruption
    • Recover from database corruption

Manage and monitor SQL Server instances (35–40%)

  • Monitor database activity
    • Monitor current sessions
    • Identify sessions that cause blocking activity
    • Identify sessions that consume tempdb resources
    • Configure the data collector
  • Monitor queries
    • Manage the Query Store
    • Configure Extended Events and trace events
    • Identify problematic execution plans
    • Troubleshoot server health using Extended Events
  • Manage indexes
    • Identify and repair index fragmentation
    • Identify and create missing indexes
    • Identify and drop underutilized indexes
    • Manage existing columnstore indexes
  • Manage statistics
    • Identify and correct outdated statistics
    • Implement Auto Update Statistics
    • Implement statistics for large tables
  • Monitor SQL Server instances
    • Create and manage operators,
    • Create and manage SQL Agent alerts
    • Define custom alert actions
    • Define failure actions
    • Configure database mail
    • Configure Policy-Based Management
    • Identify available space on data volumes
    • Identify the cause of performance degradation

Manage high availability and disaster recovery (20–25%)

  • Implement log shipping
    • Configure log shipping
    • Monitor log shipping
  • Implement AlwaysOn Availability Groups
    • Configure Windows clustering
    • Create an availability group
    • Configure read-only routing
    • Manage failover
    • Create distributed availability groups
  • Implement Failover Cluster Instances
    • Manage shared disks
    • Configure cluster shared volumes

Drop system-versioned temporal table in SQL Server

Posted on

To Drop a Temporal Table you first need to turn System_Versioning OFF. Then Delete your required temporal table. Finally you can also choose to delete History table associated with your Temporal Table.

DROP TABLE [dbo].[TemporalTest]
DROP TABLE [dbo].[TemporalTestHistory]