How to Fix Restore failed for Server while attempting 'RestoreContainer ValidateTargetForCreation'

Help me restore my SQL Server 2008 database.
by

Today I ran into the a "Restore failed for Server" error message while trying to restore a SQL Server 2008 database using a database backup (.bak) file.  Below I will show you how to fix this restore error, so that you can restore your SQL Server database (let's hope...!).

 Here's the SQL Server error message that I receceived:

Restore failed for Server 'DELL3-PC\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\GotKnowHowWAPDB.mdf'. (Microsoft.SqlServer.Smo)

So the problem I ran into and the reason I got this error message while trying to restore my SQL Server database was that the "Restore As" directory path name was different than the actual location of where the current database was located. In essence, it was using the directory (and /or file name) location from the backup (.bak) file, which was different than that of the current database I was restoring.  To understand what I mean, follow these basic restore steps:

  1. Open Microsoft SQL Server Management Studio
  2. Right click on the database you want to restore, then in the menu -> choose Tasks -> Restore -> Database...
  3. Now in the SQL Server "Restore Database" pop-up box, select the backup set to restore.
  4. Then on the left side under "Select a page", click on "Options"
  5. In the Restore options section, I usually will select "Overwrite the existing database (WITH REPLACE), NOTE: If you overwrite the database, just make sure that before restoring, you have a backup located else where in case something goes wrong.
  6. HERE's where the "Restore failed" problem arose for me:
    • In the Options page, under the "Restore the database files as:" section, the "Restore As" directoy path name was different than that of the current database that I was trying to restore.  
    • So I simply needed to change the directory name of both the .mdf file and the log (.ldf) file for the database  to the location of the current database (so they match). 
    • Note: You will also need to make sure that the "Restore As" file names are the same name of the database you are restoring (in the past I've run into differing file names, causing a problem restoring databases). 
  7. Once you've made sure that the "Restore As" directory path and file names for the (.mdf & .ldf) files are the correct location to the current database you are restoring, click "OK"
  8. That's it!  

Here's what the "Restore As" location was taken from the backup file (.mdf & .ldf):

  • c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\gotknowhowwapdb.mdf
  • c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\\gotknowhowwapdb.ldf

Here's what I changed the "Restore As" location to, in order to point to the actual location of the current database I was restoring:

  • c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\GotKnowHowWAPDB.mdf
  • c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\GotKnowHowWAPDB.ldf

So essentially, when restoring a SQL Server database using a backup file (.bak), make sure that the "Restore As" directory path and file names are the location of the current database you are restoring, and not the directory and/ or file names of where the (.bak) file database was located.

Hope that all makes sense, and helps you start restoring your SQL Server databases without any problems!

 


0
33

33 Comments

anonymous by Marie G on 3/31/2011
Thank you so much.
Your post really helped me out.
Doug by Doug on 3/31/2011
Glad it helped Marie.
anonymous by dave on 8/31/2011
thanks !
anonymous by Tri on 11/20/2011
thank you very much. you are great
anonymous by Ismawie on 1/5/2012
Thank you so much..easy now :)
anonymous by Jan on 2/28/2012
Thanks a lot - after upgrade of the SQL Server (to R2 version) I couldn't restore my previous backup files. I didn't understand the strange error. This article helped me very much!
anonymous by Amarjeet on 4/4/2012
Thanks for the post... :)
anonymous by Matt on 4/24/2012
Thanks!
anonymous by deepu on 5/7/2012
thank you so much ..its cleared my problem on 2008 r2 ..thank you..
anonymous by Ballal on 5/11/2012
Thanks a lot Doug! Your post was really helpful!
Doug by Doug on 5/11/2012
Sure thing...

Just wanted to make a note, that in SQL Server 2012 this issue is pretty much resolved (or made much less complicated) because SQL Server now has a "Restore database files as" selection that makes it a simple one step process to relocate all the files to the current database target folder/ files, so you should not get the wacky error if you selected "Relocate all files to folder" option when restoring your database in SQL Server 2012.
anonymous by Mansour on 6/4/2012
thank you verey much for this solution, i spend more than 20 hours searching without any result.
anonymous by Dhewalito on 6/26/2012
U my friend, r a GENIUS. bin battling with this for 2 days straight. thanks!!!!!
anonymous by Sarang on 8/11/2012
Thanks dude.. I encountered this issue in 2012 only.
anonymous by Guy on 8/13/2012
Thanks, this was my issue too in SQL Express 2012
anonymous by NIKE on 8/17/2012
Thank you very much.
It's very helpful
anonymous by DiaoYuIsland on 9/13/2012
thanks, it helps!!
anonymous by Sumit Gupta on 9/14/2012
Thank you, this resolved my issue
anonymous by Vaibhav on 10/30/2012
Very nices. Thks a lot
anonymous by Aikee Nhaire on 11/12/2012
Thanks! Big help...
anonymous by s7evin on 11/27/2012
Thanx dude it really helped me!!!
anonymous by infero on 11/28/2012
thanx dude very much!!!
anonymous by Gary80 on 12/14/2012
You are a life-saver
anonymous by Muhammad Junaid on 1/26/2013
Thanks A lot....
anonymous by ahmed on 2/19/2013
you are a big help me, thanks alot
anonymous by anh on 4/4/2013
thanks, very good.
anonymous by Jaime on 4/8/2013
Thank you so mach !!, it's the only method for restore this database !!!
anonymous by Dron on 4/10/2013
Sheer genius! 2 hour battle came to an end because of this.
anonymous by Anne on 4/13/2013
This was helpful and all working solution. Thanks for posting.
anonymous by patrick on 4/22/2013
Awsome, had the same issue. thank you
anonymous by Paulos on 6/1/2013
Nice one. Thanks a bunch
anonymous by vidhya on 6/3/2013
very useful.thank you so much
anonymous by Ravi on 1/18/2017

Man, You made my day. I am new to this SQL and i was struggling since yesterday. Your answer was so clear. Thanks Again

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:

Comment:
Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.

Related Posts


Are you backing up your SQL Server 2008 databases daily? You should be, especially if you don't want to lose any of your precious data that you're storing. It's incredibly easy to setup a maintenance plan in SQL Server 2008 to automatically back up one or...  more »

Here's how you can enable SQL Server Authentication in a SQL Server 2008 database, and then add a User login and password account for connecting to the database. Open SQL Server Management Studio In the Object Explorer sidebar, right-click on the top SQL...  more »

Here's a step by step process to enable a remote connection to your SQL Server 2008 Express database that is configured on Windows Server 2008 using an assigned port #. 1) First make sure the SQL Server Authentication is enabled and the User name that...  more »

When using SQL Server 2008, you may receive a Save (Not Permitted Dialog) box pop-up when trying to save changes to an existing table. The dailog box reads: "Saving changes is not permitted. The changes you have made require the following tables to be...  more »