Problem in restoring differential backup

Question

I tried to restore differential backup but i got a following error:

Restore failed for server .(Microsoft.Sqlserver.smoextended) Additional Information: system.data.sqlclient.sqlerror:The file 'astroved_data' cannot be moved by this Restore operation (microsoft.sqlserver.smo)

astroved_data is a .mdf file.

can anyone help me?

Answer 1

I can't get this error to occur in SSMS 2008, which you've specified your using, but i can get it to occur in SSMS 2005.

The reason is you cannot specify a new location for your data files when you do a differential restore under the OPTIONS page.

If you choose a new location when you restore the full backup in the options page, then you have to change it to the SAME physical location when you do the differential restore. Otherwise SSMS puts in the original location and tries to restore it to there.

This issue looks like its fixed in SSMS 2008 (Checked it with RTM) it seems to ignore any values put in on the options page for the path when doing the differential restore, even if the location is invalid.

What other options are you selecting? this may be another way of triggering the error? Try putting in the same location of the data/log files that you used in your full restore and see if it fixes it.

If you're sick of being at the mercy of SSMS then learn to do the restore in T-SQL:

-- use this command to get the logical names for the restore 
-- so you can specify a new location using MOVE
RESTORE FILELISTONLY FROM DISK='c:\av_full.bak'

-- restore the full backup, don't recover yet
RESTORE DATABASE adventureworks1 FROM DISK= 'c:\av_full.bak' WITH 
MOVE 'AdventureWorks_Data'  to 'C:\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log'   to 'C:\AdventureWorks_Log.ldf' , 
NORECOVERY

-- restore the differential
RESTORE DATABASE adventureworks1 FROM DISK= 'c:\av_diff.bak'

Answer 2

I've had this issue several times. The easiest resolution, if you are more comfortable with SSMS is to prep the restore as you did originally, but then click on the Script button in the Restore dialog.

This will spit out something similar to this:

RESTORE DATABASE [UAT] 
FROM  DISK = N'd:\Database_backup\mydifferentialbackup.bak' 
WITH  FILE = 1,  
MOVE N'mydb_Data' TO N'R:\MSSQL10_50.REPORTING\MSSQL\Data\mydb.mdf',  
MOVE N'mydb_FullText' TO N'R:\MSSQL10_50.REPORTING\MSSQL\Data\mydb.ndf',  
MOVE N'mydb_Log' TO N'R:\MSSQL10_50.REPORTING\MSSQL\Data\mydb.ldf',  
NOUNLOAD,  STATS = 10
GO

Then simply remove the MOVE clauses:

RESTORE DATABASE [UAT] 
FROM  DISK = N'd:\Database_backup\mydifferentialbackup.bak' 
WITH  FILE = 1,  
NOUNLOAD,  STATS = 10
GO

And bingo - this should work.