Cleveland DBA

SQL Server administration in Cleveland, OH

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

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_backup\TestDiff.bak'
	WITH  FILE = 1,
		MOVE N'TestDiff' TO N'F:\myserver_data\TestDiff.mdf',
		MOVE N'TestDiff2' TO N'F:\myserver_data\TestDiff_1.ndf',
		MOVE N'TestDiff_log' TO N'F:\myserver_logs\TestDiff_2.ldf',
		NORECOVERY,  NOUNLOAD,  STATS = 10
GO

But look at the differential restore:

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:\myserver_backup\TestDiff.diff'
	WITH  FILE = 1,
		MOVE N'TestDiff' TO N'F:\myserver_data\TestDiff.mdf',
		MOVE N'TestDiff2' TO N'F:\myserver_data\TestDiff.ndf',
		MOVE N'TestDiff_log' TO N'F:\myserver_logs\TestDiff.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_backup\TestDiff.diff'
	WITH  FILE = 1,
		MOVE N'TestDiff' TO N'F:\myserver_data\TestDiff.mdf',
		MOVE N'TestDiff2' TO N'F:\myserver_data\TestDiff_1.ndf',
		MOVE N'TestDiff_log' TO N'F:\myserver_logs\TestDiff_2.ldf',
		NOUNLOAD,  STATS = 10
GO

-- or

RESTORE DATABASE [TestDiff]
	FROM  DISK = N'F:\myserver_backup\TestDiff.diff'
	WITH  FILE = 1,
		--MOVE N'TestDiff' TO N'F:\myserver_data\TestDiff.mdf',
		--MOVE N'TestDiff2' TO N'F:\myserver_data\TestDiff.ndf',
		--MOVE N'TestDiff_log' TO N'F:\myserver_logs\TestDiff.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.

Advertisements

June 21, 2011 - Posted by | Troubleshooting | , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: