SQL Server find database without backup from a given date

SQL Server finds a database without a backup from a given date

Below is a sample SQL query that retrieves the list of databases that have not had a backup since a specific date:

SELECT S.NAME AS database_name,
       'No Backups' AS [Backup Age (Hours)]
FROM master.dbo.sysdatabases S
LEFT JOIN msdb.dbo.backupset B
       ON S.name = B.database_name
       AND B.backup_start_date > '20201101'
WHERE B.database_name IS NULL 
      AND S.name <> 'tempdb'
ORDER BY B.database_name;

Additional Resources:

Backup History

Restore Database

Backup History Using TSQL

How to work with a certificate in SQL Server

How to work with a certificate in SQL Server

This SQL command adds a certificate to a SQL Server database.

To perform this action, you need to have the CREATE CERTIFICATE permission on the database. It's important to note that only Windows logins, SQL Server logins, and application roles can own certificates. Groups and other roles are not allowed to own certificates.

-- Create a self-signed certificate
CREATE CERTIFICATE selfsigned
   ENCRYPTION BY PASSWORD = 'selfsigned2439587y'  
   WITH SUBJECT = 'Testing self-signed Records',
   EXPIRY_DATE = '20251031';

-- Restoring the certificate involves using the same process as creating a new certificate.
create certificate MySalaryCert
from file = N'c:\\Encryption\\MyCerti.cer'
with private key
  ( file = N'c:\\Encryption\\MyCerti.pvk'
  , decryption by password = N'hiMaNshu#paTel#dBa'
   );

CREATE CERTIFICATE MySalaryCert  
    FROM EXECUTABLE FILE = 'c:\\MySalaryCert.dll';  
GO

CREATE ASSEMBLY MySalaryCert  
    FROM 'c:\\MySalaryCert.dll'   
    WITH PERMISSION_SET = SAFE;  
GO  
CREATE CERTIFICATE MySalaryCert FROM ASSEMBLY MySalaryCert;  
GO

-- The following query displays the details of the certificate, including the public and private key information:
SELECT
  name,
  certificate_id,
  pvt_key_encryption_type_desc,
  subject,
  expiry_date,
  start_date,
  thumbprint,
  pvt_key_last_backup_date
FROM sys.certificates;

For more information, refer to the following resources:

Microsoft Docs: CREATE CERTIFICATE (Transact-SQL)

MSSQLTips: How to Configure SSL Encryption in SQL Server