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

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

TDE disabled and service restarted but still can't restore a new backup file

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
Hello, I disabled TDE on my dev database because we determined no personally identifiable info will be stored.  Disabling was done like so:Then I created a backup of MyDB database and tried to restore on a different server instance on my same machine (using SSMS) and I get  the following error: ALTER DATABASE MyDB SET ENCRYPTION OFF GO use MyDB go drop database encryption key go use master go DROP certificate DevCert GO DROP MASTER KEY GO Afterwards, I ran the following query to ensure there are no encrypted databases, and it returns zero rows: SELECT DB_NAME(database_id) AS dbname, encryption_state, case encryption_state   WHEN 0 THEN 'Unencrypted (no database encryption key present)'   WHEN 1 THEN 'Unencrypted'   WHEN 2 THEN 'Encryption in Progress'   WHEN 3 THEN 'Encrypted'   WHEN 4 THEN 'Key Change in Progress'   WHEN 5 THEN 'Decryption in Progress'   ELSE CAST(encryption_state AS varchar(20)) END AS encryption_state, key_algorithm, key_length FROM sys.dm_database_encryption_keys Then I created a backup of MyDB database and tried to restore on a different server instance on my same machine (using the SSMS) and I get the following fatal error: The media family on device 'c:\Program Files\...\Backup\TDE_removed_from_db.bak' is incorrectly formed.  SQL Server cannot process this media family. 

View Complete Post

More Related Resource Links

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)  

Multiple databases backup/restore in a transaction, if possible, into only one file


Hi dear,

I have three databases named XDW, XOLTP, XOLTPSchema. All of these are for only one application. In the application, user should do backup/restore using GUI.


1. How I should do backup/restore in one transaction? i.e. all of three actions should be success elsewhere all of them should fail.

2. Is it possible to backup them into only one file e.g. X.bak?

3. Or, What is the standard solution in this condition?

Thanks in advance.

How To: Joining multiple DataTable using “LINQ to DataSet”

sharepoint 3.0 service, database backup restore


I am using stsadm to run daily backup of the server, but would like to run hourly database backup, not sure if it works to restore database and log for sharepoint service 3.0, the db is on sql 2005 embedded edition. does any one know or has any suggestions to backup WSS3.0 hourly?

I tried the backup restore feature in the admin site, didn't get it work when last time the server crashes, so right now only use stsadm backup / restore.

File system permission error during BACKUP service master key



I'm having a strange issue with file system permissions.

A backup database to file location e:\sqlbackup works fine.
A BACKUP SERVICE MASTER KEY or BACKUP MASTER KEY fails with the error ´MSG 15240, Level 16, State 1 Cannot Write into file ´e:\sqlbackup\file.bak' Verify that you have write permissions, etc...'

What am I missing?


Backup/Restore 404 File not found error ;Site site created besed on Custom Site Template




I am using SharePoint (Version: 12.0.6318.5000). The scenario is like that;

  • I have created a Site Collection and under that I have 2 sub sites and under each sub sites again I have a sub site (Site Collection [level 1]>>>SubSite+Lists [level 2]>>>SubSite+ Lists [level 3]>>>Lists, Doc. Library and Picture Library

SQL server2008, CRM4.0, unable to read sql backup file when trying to restore



For a client of ours we have two CRM4.0 environments, both on a seperate domain. Domain A and Domain B.
Domain A is used for testing and developing pursoses, Domain B is the production environment.

The client has requested that we match the Domain A environment with the Domain B environment so that the testing and production evironments are the same before they continue with testing/development.

For this purpose I wanted to just copy the Domain A SQL database to the Domain B environment.

Unfortunately I am getting the following error when trying to restore the CRM4.0 SQL database of Domain A on the CRM4.0 SQL database server of Domain B.

System.Data.SqlClient.SqlError: Read on "C:\Users\Cen-Admin\Desktop\VWPCRM_MSCRM.bak" failed: 38(failed to retrieve text for this error. Reason: 15105) (Microsoft.SqlServer.Smo)

I have tried to follow these instructions but have been unable to complete them because of the fact that i cannot initiate the restore action:


The link that the error provides in the detailed desciption field provides no qlue to a solution:

Unable to restore from backup file when UNC path is used


I have a Visual Basic application built using Visual Studio 2008.  I connect to a SQL Server 2008 server using ODBC.  I connect to the master database and try to run the following statement:

RESTORE DATABASE [Village of Pleasant Prairie] FROM DISK = '\\\public\MyAppData\Village of Pleasant Prairie\MySQLServerData.backup' WITH REPLACE

When I do this, I get the following error:

Cannot open backup device '\\\public\MyAppData\Village of Pleasant Prairie\MySQLServerData.backup'. Operating system error 5(Access is denied.).

It may be important to note that (the address of the file server) is the same as the address of the SQL Server (both servers on running on the same physical box.  This server is also a domain controller (don't know if that is important, but it may be).

I tried changing the SQL command to:

RESTORE DATABASE [Village of Pleasant Prairie] FROM DISK = 'c:\public\MyAppData\Village of Pleasant

Restore Job has been failed with error message as "Skipped log backup file. Secondary DB"


We have already configured log shipping on server. But now restore job failed with error message as "Skipped log backup file. Secondary DB"

Kindly provide resolution steps for same.

SQL Server Backup and Restore (video)

Restore your full backup, then run a few queries before you keep restoring transaction logs. Brent shows how to use the Standby option for restores.

In this five minute video, Brent demonstrates how to do it by creating a database, populating data, and then showing what disaster recovery is like by doing RESTORE WITH STANDBY.

Toolbox: Easy File Backup, Exploring Files And Folders Inside Visual Studio, Multiple Monitor Softwa


If the responsibility for creating, managing, and executing routine backups is yours, these tools will make it easier. Also see how you can browse folders and files from inside Visual Studio.

Scott Mitchell

MSDN Magazine May 2009

SMO Backup and Restore Help

Hello Friends, I am new to SMO, Till now I have only worked with Selct,Insert/update Queries.. Now I want to Implement some backup and restore feature for my application. I looked at some documents of SMO and found it is possible using SMO. First of all I will explain my situation. My application wants to store data belongs to different companies(which is maintained with in application) in to single database server, Sql server 2008. I am planning to use Partition Scheme function and create filegroup for each company. Advantage here is, taking backup of each company will be easy. I am generating primary key for each table without taking into consideration of companies. In other words Particular table's ID 1,2 belongs to Company A, 3 Belongs to Company B and 5 belongs to Company A... But these data will be split in to Different filegroups using Company ID.(horizontal partitioning) Ok... Now the Problem.. My backup for a particular company was taken successfully. Now I want Restore function. When I am trying to restore this backup to somewhere else. Target database may or may not contain Companies installed. When Restoring Will there be any Primary key Violation? I know one solution is to generate primary key by taking into consideration Company Id. But this will cause a complete rewriting of code, as it contains lot of edit/delete queries.  I want to know, whether the

attempting to restore to a new DB and it keeps trying to save the mdf file as the original mdf file

This is using SQL Server 2005. I've had several problems and this is where I've ended up. 1. Despite the claim that this will create a new DB it doesn't.  I was getting errors about the new DB not having the specified logical file name (despite the new DB not existing).  At the time I was specifying the logical file name as it appeared in the backup. 2. if I manually create the DB I still get the error as above unless I manually change the logical file name via sql management studio.  This is meant to be an automated solution so that is not accceptable. 3. I am now giving it the logical file name of the DB that I want to restore onto (if it doesn't exist I create it), but now it's trying to save the file with the old physical file name, which is absolutely *not* what I want. My instincts tell me I need to change the logical file name in the database I'm wanting to restore onto, but I don't know how to do this via SMO.  All of the explanations I've seen are through the SQL Management Studio and that is not acceptable as this needs to be an automated solution. I have copy/pasted the code from the msdn examples verbatim changing only the dbName and the .bak file location and it has consistently failed. Here is the code in it's current incarnation. var serverName = @"myServer"; var dbName = "MIQDesignTest3"; var backupFileN

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!

What happened to the backup and restore classes in namespace Microsoft.SqlServer.Management.Smo vers

I do not see these two classes in version 10 of this namespace and I am not finding any documentation explaining what happened to them.

Error in RESTORE in LOG FILE. Anyway to exclude it?

I'm trying to install a database (on a SQL Server 2008 machine) with test data sent by a partner company in the form of a .BAK file. (Version of SQL Server they are using unknown). I'm using this script: Use Master RESTORE DATABASE XEMS FROM DISK = 'C:\temp\xemstest6-29.bak' WITH REPLACE, MOVE 'XEMS' TO 'E:\XEMS DATABASE\XEMS.MDF', MOVE 'XEMS_Log' TO 'E:\XEMS DATABASE\XEMS_Log.LDF' The Restore process churns for a while, then I get: Processed 2576 pages for database 'XEMS', file 'XEMS' on file 1. Processed 5 pages for database 'XEMS', file 'XEMS_log' on file 1. Msg 3283, Level 16, State 1, Line 2 The file "XEMS_log" failed to initialize correctly. Examine the error logs for more details. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. At this point the database is unusable - trying to do anything with it produces an error indicating the database is still in a Restore state. There is some problem with the LOG portion of the backup, which I really don't need anyway. I'd like to just NOT restore the log. Came across this article which seemed to refer to the exact problem I'm having...  http://support.microsoft.com/kb/915385/en-us They offered this suggestion: "Use the WITH NO_LOG clause during the restore process." However - in checking the RESTORE syntax.... http://msdn.microsoft.com/en-us/libr

SQL Server backup failing on one file

Hi I have a server which is not backing up a db each day. When I look at the event log it displays the following...   SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\########\########_File_CS_DefaultData.ndf] in database [########] (5). The OS file handle is 0x000006A0. The offset of the latest long I/O is: 0x00000008e00000 2010-08-16 16:07:38.19 Backup Error: 3203, Severity: 16, State: 1. 2010-08-16 16:07:38.19 Backup Read on "D:\########\########_File_CS_DefaultData.ndf" failed: 1117(failed to retrieve text for this error. Reason: 15100) 2010-08-16 16:07:38.20 Backup Error: 3041, Severity: 16, State: 1. 2010-08-16 16:07:38.20 Backup BACKUP failed to complete the command BACKUP DATABASE ########. Check the backup application log for detailed messages. 2010-08-16 16:12:14.32 spid5s SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\########\########_File_CS_DefaultData.ndf] in database [########] (5). The OS file handle is 0x000006A0. The offset of the latest long I/O is: 0x00000009900000 2010-08-16 16:13:07.02 Backup Error: 3203, Severity: 16, State: 1. 2010-08-16 16:13:07.02 Backup Read on "D:\########\########_File_CS_DefaultData.ndf" failed: 1117(failed to retrieve text for this error. Reason: 15105) 2010-
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