SQL Server certificate for backups: Does expiration date matter?
I have tested this on SQL Server 2016 (Developer Edition, though that shouldn't matter), and can confirm:


No, after the Certificate expires, using it to encrypt a backup will cause the following error:


  Msg 3096, Level 16, State 1, Line xx
  The Certificate specified for backup encryption has expired.
  Msg 3013, Level 16, State 1, Line xx
  BACKUP DATABASE is terminating abnormally.

Yes, after a Certificate expires, backups encrypted from that Certificate can still be restored. BUT, you still need to have the Certificate loaded into master in order to do the RESTORE.


Just create a new Certificate, and be sure to specify the EXPIRY_DATE = 'utc_date' clause, else the expiration date will default to 1 year from the moment it was created.

As to why Certificates have expiration dates, that is probably to give more control over the security mechanism, similar to how you can set a password policy for Logins to expire. Certificates can be backed-up and used in multiple places, so it can help ensure that you don't have a piece of information sitting out there, somewhere, forever that can be used to compromise your system.