Particularly, if I encrypt a database can I send a backup of it over a non-secure FTP with relatively high assurance it cannot be accessed?
View Complete Post
I have five databases sitting on a new SQL 2008 R2 server. The databases originated on SQL Server 7, went through both a SQL 2000 and a SQL 2005 upgrade, and last month were migrated to this new SQL 2008 server. All five databases are in SQL
2005 compatibility mode. All five databases are using SQL 2008's full database encryption.
Over the weekend, I changed the page verification of these databases from "torn page" to "checksum". Nightly full backups were completed on Sunday night, and successfully restored to our reporting and hot-standby servers. Yesterday, I added the
WITH CHECKSUM keyword to the backup jobs. Nightly full backups ran last night, completed successfully. The restores of those same backups to reporting and standby FAILED, with several assertion failure messages.
Today, I created a brand new database, in SQL 2008 compatibility mode and "checksum" page verification. BACKUP WITH CHECKSUM produces a restorable backup. I then enabled encryption on that database, and did the same BACKUP WITH CHECKSUM operation.
The resulting backup cannot be restored, NOT EVEN on the same server. The restore appears to complete, giving the normal output stating how many pages it processed, but the "recovery" portion of the restore throws an exception.
The ReportServer database (and ReportServerTempDB) contains encrypted data...but when all the databases on an instance are encrypted using TDE, are there any issues with also encrypting the ReportServer/ReportServerTempDB database that happen to be on that
same instance? (I am not concerened with performance issues, unless they would definitely be severe for SSRS.)
Good day all,
Well this problem first occurred for me when moving from SQL2008 to SQL2008R2. Now I have reinstalled my database machine again and am going from 2008R2 to 2008R2 - same version this time, but same problem.
I have restored my master key and certificate successfully, and the restore seems to complete according to the status output, but the query executes infinitely and the database even after server restart is inaccessible.
Here is the restore code and output: