Assume I have sql server 1 (crashed unrecoverable OS) and sql server 2 (fresh install). Server 2 uses a sql service account that is different to server 1.

Assume I have the SMK backup file from server 1, created using the following command:


Suppose I want to rebuild sql server 1 onto sql server 2. Then as 1st step say I restore the master db. Then to restore the SMK:


The documentation about RESTORE SERVICE MASTER KEY says: https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-service-master-key-transact-sql?view=sql-server-ver16#remarks

When the service master key is restored, SQL Server decrypts all the keys and secrets that have been encrypted with the current service master key, and then encrypts them with the service master key loaded from the backup file.

Since the sql svc account is different on the second server, it won't be able to decrypt the existing SMK from master db. Therefore it cannot decrypt the secrets/keys.

The solution is to use force keyword. I have tried this but want some clarity on how the force keyword works because the documentation mentions chance of data loss:

Forces the replacement of the service master key, even at the risk of data loss.

I found a webpage that says this (force) doesn't work - https://www.sqlservercentral.com/forums/topic/cannot-restore-service-master-key. But everyone on that page seem confused and miss the point that it is a simple case of missing permissions. So this example is irrelevant and can be ignored. (I only put this example here to help others who are in this situation)

Couple of links below that says this (force) just works;

When I run the RESTORE without force keyword I get following error which is understandable:

The current master key cannot be decrypted. If this is a database master key, you should attempt attempt open it in a session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

When I try with the Force option, I get following output (note this is not an error. It is a success message output):

The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

I tested the linked sever, credentials on the server 2 and the restore seems to have worked fine.

Clearly as per above message the restore initially failed and then it did a force replacement. This question is to ask when/what exactly does it mean by risk of data loss upon using the force keyword?

1 Answer 1


What exactly is your question?

The documentation for RESTORE SERVICE MASTER KEY states:

If a dependent key cannot be decrypted during a forced restore, data that is secured by that key will be lost.

The documentation for service master key says the following (my emphasis):

The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

If you have access to the Windows credentials (that is, the user name and password) from the original service account, you won't have data loss. That appears to support your conclusion.

I've voted to close this, but I'm biased because I work at Microsoft on the SQL Docs content team, so I want to make sure that the documentation on this is clear. You have the credentials, so you can recover the SMK, so you can restore your data.

  • Reason for asking this question is that I cannot find any clarity about what data loss to expect so that I can check all areas as part of my testing.
    – variable
    Commented Aug 29, 2022 at 6:06
  • If you can successfully restore the database and run a query, there's no data loss in this context. Microsoft won't corrupt parts of your database, because it's all encrypted using the same algorithm. As I commented on another question you asked on this site, you are in need of training so that you can succeed in your role as a data professional, and I want you to succeed because you are inquisitive and clearly want to learn. Please get some training. Commented Aug 29, 2022 at 6:11
  • 1
    Sure but being able to simply query a database does not necessarily mean the restore SMK went OK, and is a misleading statemrnt to make. Your point is valid only assuming that the database has certain columns encrypted using the DMK. Hence I ask what areas do I need to expect data loss checks. For example I already tested linked server, credentials and databases that has DMK/encryption on it.
    – variable
    Commented Aug 29, 2022 at 6:15
  • That's why I said "and run a query". The security hierarchy starts with the SMK. Let's say you have an encrypted column in a database on server1. You recover the SMK and DMK, and restore the database with that encrypted column on a new server. If you can query that encrypted column on server2, you have not lost any data. Commented Aug 29, 2022 at 6:23
  • You recover the SMK and DMK, and restore the database with that encrypted column on a new server - isn't it sufficient for me to only restore (recover) the SMK and the db? It will auto recover the DMK without me needing to do this DMK recovery myself.
    – variable
    Commented Aug 29, 2022 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.