Error: “The file … cannot be moved by this RESTORE operation.”

This entry was posted in Troubleshooting and tagged differential backup differential restore error on by

This morning, I was trying to restore a database from a SQL 2005 backup to a new server.  The restore involved a full backup and a differential.  I restored the full backup with NORECOVERY via SSMS, but when I tried to restore the differential, also via SSMS, I got this error:

Okaaay.  I was a bit befuddled until I scripted out the two restore commands that SSMS is actually generating.  The full restore is fine:

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:myserver_backupTestDiff.bak'
	WITH  FILE = 1,
		MOVE N'TestDiff' TO N'F:myserver_dataTestDiff.mdf',
		MOVE N'TestDiff2' TO N'F:myserver_dataTestDiff_1.ndf',
		MOVE N'TestDiff_log' TO N'F:myserver_logsTestDiff_2.ldf',
		NORECOVERY,  NOUNLOAD,  STATS = 10
GO

But look at the differential restore:

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:myserver_backupTestDiff.diff'
	WITH  FILE = 1,
		MOVE N'TestDiff' TO N'F:myserver_dataTestDiff.mdf',
		MOVE N'TestDiff2' TO N'F:myserver_dataTestDiff.ndf',
		MOVE N'TestDiff_log' TO N'F:myserver_logsTestDiff.ldf',
		NOUNLOAD,  STATS = 10
GO

Notice anything wrong? The physical filenames aren’t the same as the ones used in the first restore. So SQL Server rightfully assumes you’re trying to move datafiles whle performing a differential restore and spits out an error. If you change the physical filenames in the differential restore to match the first restore, or remove the MOVE clauses altogether, it works:

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:myserver_backupTestDiff.diff'
	WITH  FILE = 1,
		MOVE N'TestDiff' TO N'F:myserver_dataTestDiff.mdf',
		MOVE N'TestDiff2' TO N'F:myserver_dataTestDiff_1.ndf',
		MOVE N'TestDiff_log' TO N'F:myserver_logsTestDiff_2.ldf',
		NOUNLOAD,  STATS = 10
GO

-- or

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:myserver_backupTestDiff.diff'
	WITH  FILE = 1,
		--MOVE N'TestDiff' TO N'F:myserver_dataTestDiff.mdf',
		--MOVE N'TestDiff2' TO N'F:myserver_dataTestDiff.ndf',
		--MOVE N'TestDiff_log' TO N'F:myserver_logsTestDiff.ldf',
		NOUNLOAD,  STATS = 10
GO

This appears to be a bug in SSMS but I’ve only gotten it to happen under specific circumstances. The backups needed to be from a SQL 2005 instance, but you could be restoring to SQL 2005 or SQL 2008.  And it looks like you need to be restoring a database with multiple datafiles.  These were the 2 conditions I personally experienced the problem with, though I didn’t do exhaustive testing.  If anyone finds any other scenarios where this happens, feel free to leave a comment.  Hopefully this saves others some time.

Also recommended:

Leave a comment

Your email address will not be published.

7 thoughts on “Error: “The file … cannot be moved by this RESTORE operation.””

  • Reply ↓
    Spenser Aden

    Thanks for the tip, this just saved me a lot of time!

    I have the exact same scenario, full + diff and multiple datafiles, only difference though is that my backups and destination are all SQL 2008 R2, no 2005.

  • Reply ↓
    Peter Tilsted

    Thanks for your posting
    I was going crazy from this message, i was not trying to move anything 🙂

    Seems the problem in my case (2008R2) happened only when the physical location at the time of the backup, was different than the database location used to restore

    regards

    • Reply ↓
      Peter Tilsted

      this may be more understandable

      Seems the problem in my case (2008R2) happened only when the physical location of the database at the time of the backup, was different than location of the database at the time of the restore

  • Reply ↓
    HP

    This really resolved my issue, thanks for explaining in detail i spent about an hour messing around this until i came to your blog, and then within 10 minutes i resolved the whole issue… thanks and cheers

  • Reply ↓
    Richard Milne

    Thank you this was incredibly helpful, We had our Full on Backupexec tape and our differentials on a NAS and it was causing us some grief trying to restore correctly.