none
Restore to a new database failed

    Question

  • Hello,  I have a database backup taken from SQL Server 2005.  I originally had placed the backup into a file in My Documents, but when I went to locate the backup file there (C:/Documents and Settings...), the folders would not expand.  So I moved the backup to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup directory and was able to select the file there.  When I try to restore this file into SQL Server 2005 Express using the  Management Studio, I get the following error message:

     

    Restore failed for Server 'DELL670DESKTOP\SQLEXPRESS'.  (Microsoft.SqlServer.Express.Smo)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
       at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()

    ===================================

    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\MSSQL.1\MSSQL\sequoia.mdf'. (Microsoft.SqlServer.Express.Smo)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476

    ------------------------------

     

    Can you please help me figure out what I am doing wrong? 

     

    Thanks so much, LC

    Wednesday, August 01, 2007 9:19 PM

Answers

  • In this section of the error message:

    ===================================

    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\MSSQL.1\MSSQL\sequoia.mdf'. (Microsoft.SqlServer.Express.Smo)

    ------------------------------

    you can see that the restore is attempting to put the file in the MSSQL folder -NOT the BackUp folder.

     

    You may wish to explore using the 'WITH MOVE ' parameter for the RESTORE command in order to RESTORE the backup to a different location.

     

    Refer to Books Online, Topic: RESTORE

     

    Wednesday, August 01, 2007 11:26 PM
    Moderator

All replies

  • Make sure the database file does already exist. You will need to choose a different name for the database.

     

    Wednesday, August 01, 2007 9:45 PM
  • I did select a new name for the database.  What do you mean "make sure the database file does already exist"?  Or did you mean "doesn't"?

     

    Wednesday, August 01, 2007 10:40 PM
  • In this section of the error message:

    ===================================

    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\MSSQL.1\MSSQL\sequoia.mdf'. (Microsoft.SqlServer.Express.Smo)

    ------------------------------

    you can see that the restore is attempting to put the file in the MSSQL folder -NOT the BackUp folder.

     

    You may wish to explore using the 'WITH MOVE ' parameter for the RESTORE command in order to RESTORE the backup to a different location.

     

    Refer to Books Online, Topic: RESTORE

     

    Wednesday, August 01, 2007 11:26 PM
    Moderator
  • Thank you so much - that did the trick!

     

    Thursday, August 02, 2007 12:15 PM
  • Thanks Arnie,

     

    This solved also my problem!

     

    Kind regards

    Tom

     

    Saturday, April 26, 2008 5:37 AM