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.
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
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:
But on the SQL Server 2012 machine, that folder doesn't exist; it is going to be:
(The differences may be even more pronounced if either is a named instance.)
So you have two options at this point:
- 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.
WITH MOVEand 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