4

Does using WITH REPLACE when the logical filenames match (to avoid using WITH MOVE) fail when the backup is from a different version of SQL? I have a backup from SQL 2008 but when I attempted to restore it using WITH REPLACE on a SQL 2012 folder, despite the Logical filenames matching on the database backup and the database I'm replacing, it's failing giving me the 'directory lookup' error.

  • Please add the actual error message, and the actual RESTORE command, etc. Always err on the side of giving too much detail! – Max Vernon Aug 8 '14 at 20:00
4

This is the answer I've found for why the file autolocation doesn't work in SQL 2012 for me. I don't believe it has anything to do with the backup being from SQL 2008.

In SQL Server 2012, during the restore of a full backup without MOVE clauses that indicate the target physical location of each file, on top of an existing database, the file autolocation algorithm requires: not only a logical name match between the file declared in the backupset and any given file in the existing database, but also their file GUID, file ID, and create LSN must match. If any of those attributes don’t match, the physical location used for that logical file is the one stored in the backupset. This additional requirement was implemented to fix this last scenario I just exposed which was considered a defect.

This was taken from this MSDN article: http://blogs.msdn.com/b/ialonso/archive/2012/06/08/sql-server-changes-behavior-in-cases-where-file-autolocation-is-invoked-during-the-planning-phase-of-a-full-restore-with-replace-clause.aspx

1

WITH REPLACE refers to the database, not its files (though the net effect would be the same I guess).

If you have file names that match an existing database and you try to restore it as a different database, you will need to use WITH MOVE to prevent SQL Server from trying to overwrite the existing files (which it won't be able to do).

What's happening in your case, though, almost certainly, is that your database backup from 2008 has its data files in a path like:

C:\...\MSSQL.100\...\Data\

But on the SQL Server 2012 machine, that folder doesn't exist; it is going to be:

C:\...\MSSQL.110\...\Data\

(The differences may be even more pronounced if either is a named instance.)

So you have two options at this point:

  1. Determine the folder(s) the backup expects, using RESTORE FILELISTONLY, and create those folders, making sure that the SQL Server service account has read/write/modify permissions.
  2. Use WITH MOVE and put the files in a valid location.

A third, extremely silly option that I only mention for completeness is that you could go back to the 2008 server, move the database to a folder that matches a folder that exists on the new server, take a new backup, and then restore. Don't do this. Just stop trying to avoid typing WITH MOVE...

  • Aaron, thank you for the comments. I thought that if the logical filenames (not the physical filenames) matched on the database you were replacing that SQL server would automatically put the files into the same paths as the existing database (not the paths stored in the backup file). I've successfully done this before (going from SQL 2008 backup to SQL 2008 server) but now I'm not sure if the physical filenames matched as well as the logical names. My automated restore SPROC does look at FILELISTONLY and attempt to determine if a WITH MOVE statement is required. I may need to tweak it. – Brad Aug 9 '14 at 13:06
  • I found this article which explains my situation entirely: blogs.msdn.com/b/ialonso/archive/2012/06/08/… Thoughts? – Brad Aug 9 '14 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.