SQL Error: 3283, Severity: 16, State: 1

2017-08-31

When I tried to restore a database backup to a new server with SQL Server 2008 R2, I got this weird message - _The file “mydb_log” failed to initialize correctly. Examine the error logs for more details.” Error: 3283, Severity: 16, State: 1. _I suspect the backup file was corrected. So I took another backup from the source. Tried the restore again. But it failed again with the same exact message. So I did some research. Found out the source database turned on TDE before, but it got turned off. It is a bug Microsoft still didn’t fix. I had to import the Certificate which was used for TDE to the new SQL Server. Then did restore again. It was successful. Now learned another trick.

<span class="kwrd">USE</span> master; 
<span class="kwrd">GO</span> 

<span class="kwrd">CREATE</span> MASTER <span class="kwrd">KEY</span> ENCRYPTION <span class="kwrd">BY</span> PASSWORD = N<span class="str">'Passw0rd!'</span>; 
<span class="kwrd">GO</span>


<span class="kwrd">CREATE</span> CERTIFICATE TDECert
<span class="kwrd">FROM</span> <span class="kwrd">FILE</span> = N<span class="str">'D:\TDECert.certbak'</span>
<span class="kwrd">WITH</span> PRIVATE <span class="kwrd">KEY</span>
( <span class="kwrd">FILE</span> = N<span class="str">'D:\TDECert.pvkbak'</span>
, DECRYPTION <span class="kwrd">BY</span> PASSWORD = N<span class="str">'Passw0rd!'</span>
);
GO