.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Is backup encrypted if database is encrypted?

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server

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

More Related Resource Links

BACKUP WITH CHECKSUM against an encrypted database - restore fails


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.

SQL Server 2008 TDE - should the ReportServer database be encrypted with TDE


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.)


Restoring an encrypted database (TDE) in SQL2008R2 executes forever.


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:


SQL2008 - Cannot Restore a new database from a full backup of a different one, database in use

I am trying to restore a brand new database from a copy of another one and am encountering an error message stating: System.Data.SqlClient.SqlError: The file <Backup source database file location> cannot be overwritten.  It is being used by database <Backup Source Database>. (Microsoft.SqlServer.Smo) I am running the Restore procedure with the REPLACE option and am wondering why it is stating that the source database is the database in use when I am trying to restore and overwrite a completely different database. This issue happens when running the replace both in C# using SMO and when manually restoring with SqlServer Management Studio Interesting note is, the original source database is created / deployed using a database project within VisualStudios 2010 and then deployed to SqlServer through VisualStudios. Any thoughts on why this is happening? Thanks in advance for any help!

Coding is not working for Making Password Field Encrypted in sql and using sessions .basically its a

Dim cn As SqlConnection         Dim cmd As SqlCommand         Dim dr As SqlDataReader         cn = New SqlConnection         cn.ConnectionString = ConfigurationManager.ConnectionStrings("fees_systemConnectionString").ToString         cn.Open()         cmd.Connection = cn         cmd.CommandType = CommandType.Text         Dim Hashpass As String = FormsAuthentication.HashPasswordForStoringInConfigFile(password.Text, "sha1")         cmd.CommandText = "Select * from login where username='" & Me.txtUsername.Text & "' and password = '" & Hashpass & "'"         Dim re As SqlDataReader = cmd.ExecuteReader()         If re.HasRows Then             While re.Read()                 Session("email_id") = re("username").ToString()                 Session("MemberType") = re("type").ToString()                 Session("ISLOGIN&

Can't restore database backup file in my database ? using sql server 2005. please help

VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

How to Include Password during Database Backup over Tape

Hi All, We have EMC Backup Solution in our organization which does backup of all the databases. I want to know is there any way that i can include the password option whenever Backup runs. I dont want to modify any script at the EMS Backup solution side. I want to do it on the fly.         Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Why SQL accepts both encrypted connection and non-encrypted connections when force encryption option

I have installed a certificate and have set force encryption to yes under the flags' tab of protocols' window as well as SQLnative client configuration properties but SQL Server accepts both encrypted connection and non-encrypted connections,why?(i have checked the encrypt connection of the connection properties of connect to server window). also when i run profiler, it can capture both T-SQL statements which has run against encrypted and non-encrypted connection and i expect the profiler should not be able to get the encrypted connection's T-SQL statement,am I right or something is wrong with my  configurations?--yousef

Encrypted and Decrypted Field.

Hiii All; Hope you are doing well .. I want to encrypt all the data under one of the column we got here. How can i do that?! And what shall i do to decrypt it? The current data is something like this: Name:           Number: Smith            123456 Gorge           938483 So I want it to be like this: Name:            Number: Smith             XXXXXX Gorge            XXXXXX   Thank you.

Is there a way to backup all my store procedures under a particular SQL 2005 database?

hi all, Is there a script, doc, VBS script, SQL statement out there which will read all my store procedures and save them as a TXT file? Currently I have to save all of them in text files before I update them when I get a new web site release. Thank you  JCD

how to restore msdb database on sql 2008 from sql 2005 backup?

Currently, I am unable to do it. It gives me the error than I cannot be restored because it was created by a different version of the server. What is the work-around? I hate scripting all my maintenance plans and alerts, there are tonns of themJulieShop

"The backup set holds a backup of a database other than existing database. Restore Database is term

I have a problem when i restore my .DAT_BAK file.  I am getting error like "The backup set holds a backup of a database other than existing database.  Restore Database is terminating abnormally".   I tried by using   RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:\DATA\MYTEST.DAT_BAK' WITH MOVE 'VZAI_DATA' TO D:\PROGRAM FILES\..\MSSQL\TEST.MDF', MOVE 'VZAI_LOG' TO D:\PROGRAM FILES\..\MSSQL\TEST.LDF', REPLACE   And also i tried like   RESTORE DATABASE <DATABASENAME> FROM DISK = 'D:\DATA\MYTEST.DAT_BAK' WITH REPLACE   When i use like this,   RESTORE FILELISTONLY FROM DISK = 'D:\DATA\MYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.   Can i anyone please help me out?   Thanks in Advance, Anand Rajagopal

Encrypted network traffic

I have an MSSQL 2000 server that has an SSL certificate. In the SQL Server network utility, 'Force protocol encryption' is checked. Looking at the traffic with Wireshark, it's no longer in plain text. However, the certificate is issued to say a.mydomain.com. If I create a dns record such that b.mydomain.com resolves to the same address and then use Query Analyzer to connect to b.mydomain.com everything still works. I get no sort of warning like I would if I used a web browser to visit an https site where the certificate had been issued to a different name. Is this to be expected? Regards Richard

calculating the size required for backup of database

Hi, How to calculate the size required to take backup of a database in mssql 2000.the results returned by sp_spaceused differ too much from the actual size taken on the disk. Thanks in advance

Export Encrypted Certificate

Hi, I have successfully created the neccessary encryption certifaicates/keys/master key on a local sql server instance. I would like now to export that same settings to a new client instance so that we can do the following: Make sure that only licensed users are able to log in to the system. So the way I thought I could do this was to create a master encrypted value on our main server and then export this certificate to the client db. Then I would store encrypted values (from our main server) in the client user table. Upon logging into the app the application would attempt to decrypt these values using the orginal installed certificate, upon successful decryot to a value that the application will recognise the user will then be able to log in. Is this sort of thing possible using sql server. I have succesfully restored the certificate onto a new instance but upon running the decrypt it does not recognise the encrytpted value. Any help/suggestions/links would be most most welcome/ Thanks in advance

: Exclusive access could not be obtained because the database is in use.could not applu log backup f

I have 50 databases all are log shippipped.i am getting below error for some databases some time 2010-09-07 15:45:21.04 *** Error: Could not apply log backup file '\\ds\M$\CT_20100907190004.trn' to secondary database 'dsCT'.(Microsoft.SqlServer.Management.LogShipping) *** 2010-09-07 15:45:21.04 *** Error: Exclusive access could not be obtained because the database is in use. I have selected option to disconnect all users when database is in restoring state also. Backup is schedule to run at every hour suppose 12 copy will run at 12:15 and restore will run a 12:45pm. Urgent because i am getting this error on all servers  

Full database backup takes so long

Today several full database backups took so long. Normally it took 2 hours to backup. But today the 5 backups didn't finish by the end of the work day. The backups went well until yesterday. I don't know what happened overnight. Is there a performance tool embedded in SQL Server? How can I find out why the backups take so long? What could be the possible reasons? Thank you,  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend