We just would like to be able to tell if it is already done or not. Anyone have any tricks up their sleeve?
Well, yes, actually.
As explained more fully in the following answer of mine on a very similar question:
How do I check that the database master key encryption is valid?
since the Service Master Key (the correct one) is needed to use the Database Master Key for operations that do not explicitly specify a password, you just need to attempt using the Database Master Key. If the operation succeeds, then the current Service Master Key is the correct one. But if it fails, then you know that the Database Master Key needs some other Service Master Key.
Two simple-enough options are:
If you have a Certificate that is guaranteed to exist in the Database being restored, try using it:
USE [newly_restored_db];
IF ( SIGNBYCERT( CERT_ID( '{certificate_name}' ), 'test' ) IS NULL)
BEGIN
OPEN MASTER KEY DECRYPTION BY PASSWORD = '{password}'; -- password used to protect DMK
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '{password}';
CLOSE MASTER KEY;
PRINT 'All fixed, yo!';
END;
ELSE
BEGIN
PRINT 'All good, yo!';
END;
If no Certificate is guaranteed to exist in the Database being restored, then try to create one. If the SMK can be used to automatically decrypt the DMK, then the Certificate will be created, else the operation will fail:
USE [newly_restored_db];
BEGIN TRY
CREATE CERTIFICATE [TestCert] WITH SUBJECT = 'yadda yadda yadda';
DROP CERTIFICATE [TestCert];
PRINT 'All good, yo!';
END TRY
BEGIN CATCH
OPEN MASTER KEY DECRYPTION BY PASSWORD = '{password}'; -- password used to protect DMK
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = '{password}';
CLOSE MASTER KEY;
PRINT 'All fixed, yo!';
END CATCH;