none
RESTORE DATABASE WITH REPLACE Fails in SQL 2012 (Works in 2005/2008)

    Question

  • I have a commercial application that installs a database instance and restores a database from a .BAK file. I must be able to support SQL 2005/2008 and 2012. The instance of the database is created during the install and it's data root is a subdirectory of the install directory which can be anywhere the user chooses.

    To accomplish this I BACKUP the database from a SQL 2005 server and during the install attach to the SQL instance and execute the following SQL statements:

    CREATE DATABASE databasename
    RESTORE DATABASE databasename FROM DISK = 'X:\InstallDir\databasename.Bak' with replace

    During the restore in 2005 the sql  has no problems even if the database files are in a different directory.

    During the restore in 2008 the sql database is converted from 2005 to 2008 and has no problems even if the database is in a different directory.

    BUT in SQL 2012 I get the following error: (Even when the root is identical to the .BAK file root)

    eb1>Directory lookup for the file "X:\InstallDir\Database\MSSQL.2\MSSQL\DATA\databasename.mdf" failed with the operating system error 3(The system cannot find the path specified.).
    File 'databasename' cannot be restored to 'X:\InstallDir\Database\MSSQL.2\MSSQL\DATA\databasename.mdf'. Use WITH MOVE to identify a valid location for the file.
    Directory lookup for the file "X:\InstallDir\Database\MSSQL.2\MSSQL\DATA\databasename_log.LDF" failed with the operating system error 3(The system cannot find the path specified.).
    File 'databasename_log' cannot be restored to 'X:\InstallDir\Database\MSSQL.2\MSSQL\DATA\databasename_log.LDF'. Use WITH MOVE to identify a valid location for the file.
    Problems were identified while planning for the RESTORE statement. Previous messages provide details.
    RESTORE DATABASE is terminating abnormally.
    State:37000,Native:5133,Origin:[Microsoft][SQL Server Native Client 11.0][SQL Server]
    State:37000,Native:3156,Origin:[Microsoft][SQL Server Native Client 11.0][SQL Server]
    State:37000,Native:5133,Origin:[Microsoft][SQL Server Native Client 11.0][SQL Server]
    State:37000,Native:3156,Origin:[Microsoft][SQL Server Native Client 11.0][SQL Server]
    State:37000,Native:3119,Origin:[Microsoft][SQL Server Native Client 11.0][SQL Server]
    State:37000,Native:3013,Origin:[Microsoft][SQL Server Native Client 11.0][SQL Server]</eb1>

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

    The directory that was created on disk is:

    X:\InstallDir\Databases\MSSQL11.InstanceName\MSSQL\DATA

    This seems to be a bug???

    On an aside, I also had to remove the command line switch  /SQLSYSADMINACCOUNTS="Username" from the SQL 2012 instance install because of a known bug in SQL Server 2012.

    Monday, July 29, 2013 9:21 PM

Answers

  • You are getting this error as when it is trying to restore because service account (SQL server service account with which it is running) does not have permission on X drive .Make sure you have read write modify permission there .Why dont you  try this restore format ,you dont need to create DB before restoring in this case.

    make sure which ever drive ur restoring proper rights is there.Also make sure drive on which ur backup is paced sql server service account has rights there

    restore filelistonly from disk='backup_location' go --take logical filename for data and log from above-- RESTORE DATABASE YourDB FROM DISK = 'D:\YourBaackUpFile.bak' WITH MOVE 'YourMDFLogicalName' TO 'D:\DataYourMDFFile.mdf', MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers




    Tuesday, July 30, 2013 6:22 AM
  • If you would specify a file  location while CREATE DATABASE databasename
    as the same  as databasename.Bak have yopu won't get the error.

    While this is true. I also use the fact that the "With replace" works in 2005 and 2008 for backup and offsite laptop versions of the Database that are synched back to the main database when used. The backup to these machines is done using the current functioning BACKUP database/RESTORE database with replace functions. If I change the product to support 2012's failure of the "with replace" I will be changing the location of these database files. I cannot do this to existing customers as they use backup tools that need to know the location of the databases.

    Will someone acknowledge that the "with replace" should not care that the database is being moved?

    p.s: I did try this and it worked, which also proves the permission comment above is not the cause.

    Good to know that it worked.At first error seems that ,since it was not able to find file name so i assumed that might be permission issue.

    WITH REPLACE option only ovewrites your database with current backup and has nothing to do with location.generally when yuo restore from old backup taken from other server backup files stores location ofMDF and LDF from old server if same location and folders and subfolders are present in current server it will restore properly if not it will give error ...in that case you manyallu use move command in script to specify new location


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, July 30, 2013 8:42 AM

All replies

  • You are getting this error as when it is trying to restore because service account (SQL server service account with which it is running) does not have permission on X drive .Make sure you have read write modify permission there .Why dont you  try this restore format ,you dont need to create DB before restoring in this case.

    make sure which ever drive ur restoring proper rights is there.Also make sure drive on which ur backup is paced sql server service account has rights there

    restore filelistonly from disk='backup_location' go --take logical filename for data and log from above-- RESTORE DATABASE YourDB FROM DISK = 'D:\YourBaackUpFile.bak' WITH MOVE 'YourMDFLogicalName' TO 'D:\DataYourMDFFile.mdf', MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers




    Tuesday, July 30, 2013 6:22 AM
  • If you would specify a file  location while CREATE DATABASE databasename as the same  as databasename.Bak have yopu won't get the error.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 30, 2013 6:50 AM
    Answerer
  • You are getting this error as when it is trying to restore 
    because service account (SQL server service account
    with which it is running) does not have permission on X drive

    This is not the case, the directory has full permissions for Everyone.
    Tuesday, July 30, 2013 8:22 AM
  • Did u tried resrtoring it thorugh query i gave in first post by me.Did you provid same location as you previous DB (from where you took backup).Giving different location will also work.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, July 30, 2013 8:28 AM
  • If you would specify a file  location while CREATE DATABASE databasename
    as the same  as databasename.Bak have yopu won't get the error.

    While this is true. I also use the fact that the "With replace" works in 2005 and 2008 for backup and offsite laptop versions of the Database that are synched back to the main database when used. The backup to these machines is done using the current functioning BACKUP database/RESTORE database with replace functions. If I change the product to support 2012's failure of the "with replace" I will be changing the location of these database files. I cannot do this to existing customers as they use backup tools that need to know the location of the databases.

    Will someone acknowledge that the "with replace" should not care that the database is being moved?

    p.s: I did try this and it worked, which also proves the permission comment above is not the cause.

    Tuesday, July 30, 2013 8:30 AM
  • If you would specify a file  location while CREATE DATABASE databasename
    as the same  as databasename.Bak have yopu won't get the error.

    While this is true. I also use the fact that the "With replace" works in 2005 and 2008 for backup and offsite laptop versions of the Database that are synched back to the main database when used. The backup to these machines is done using the current functioning BACKUP database/RESTORE database with replace functions. If I change the product to support 2012's failure of the "with replace" I will be changing the location of these database files. I cannot do this to existing customers as they use backup tools that need to know the location of the databases.

    Will someone acknowledge that the "with replace" should not care that the database is being moved?

    p.s: I did try this and it worked, which also proves the permission comment above is not the cause.

    Good to know that it worked.At first error seems that ,since it was not able to find file name so i assumed that might be permission issue.

    WITH REPLACE option only ovewrites your database with current backup and has nothing to do with location.generally when yuo restore from old backup taken from other server backup files stores location ofMDF and LDF from old server if same location and folders and subfolders are present in current server it will restore properly if not it will give error ...in that case you manyallu use move command in script to specify new location


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, July 30, 2013 8:42 AM
  • The answers listed are correct and I would like to clarify the confussion on my part. We default install into a specific directory and this has worked for both 2005 and 2008. We restore from the default directory plus subdirectory MSSQL.2. This is working for all installs in 2005 and 2008 because most SQL use the default instance of MSSQL.1 which is SQLExpress and the next created instance (ours) is MSSQL.2. SQL 2012 decided to change the name of the subdirectory to MSSQL.InstanceName, thus our restores failed and the thought that SQL 2012 had changed behaviour.

    Thanks for all the responses.

    Monday, August 05, 2013 2:13 PM