2

I have a database, and i want to force a restore over top of it.

I check the option:

  • Overwrite the existing database (WITH REPLACE)

enter image description here

But, as expected, SSMS is unable to overwrite the existing database.

Of course i don't want different filenames; i want to overwrite the existing database.

How do i force a restore over an existing database?

And for Google search crawler:

File '%s' is claimed by '%s'(4) and '%s"(3). The WITH MOVE clause can be used to relocate one or more files.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3176)

Update

The script (before i deleted the database, because i needed to get it done) was:

RESTORE DATABASE [HealthCareGovManager] 
FILE = N'HealthCareGovManager_Data',  
FILE = N'HealthCareGovManager_Archive',  
FILE = N'HealthCareGovManager_AuditLog' 
FROM  DISK = N'D:\STAGING\HealthCareGovManager10232013.bak' WITH  FILE = 1,  
MOVE N'HealthCareGovManager_Data' TO N'D:\CGI Data\HealthCareGovManager.MDF',  
MOVE N'HealthCareGovManager_Archive' TO N'D:\CGI Data\HealthCareGovManager.ndf',  
MOVE N'HealthCareGovManager_AuditLog' TO N'D:\CGI Data\HealthCareGovManager.ndf',  
MOVE N'HealthCareGovManager_Log' TO N'D:\CGI Data\HealthCareGovManager.LDF',  
NOUNLOAD,  REPLACE,  STATS = 10

I used the UI to delete the existing database, so that i could use the UI to force an overwrite of the (non)existing database.

Hopefully there can be an answer so that the next guy can have an answer.

No, nobody was in the context of the database (The error message from other connections is quite different from this error, and i only got to see this error after i killed the other connections).

Ian Boyd
  • 5,293
  • 14
  • 60
  • 82
  • Can you share the script for the backup? (by clicking on that script button at the top of the backup window). It's difficult to understand all of the settings from the screenshot. – Brian Knight Oct 24 '13 at 15:41
  • Are there existing connections to the database? – joeqwerty Oct 24 '13 at 15:45

2 Answers2

3

You have 2 database files trying to restore to the same physical file.

MOVE N'HealthCareGovManager_Archive' TO N'D:\CGI Data\HealthCareGovManager.ndf',  
MOVE N'HealthCareGovManager_AuditLog' TO N'D:\CGI Data\HealthCareGovManager.ndf',

Change one of those physical paths and your error should go away.

squillman
  • 37,883
  • 12
  • 92
  • 146
2

It looks like you are trying to write two of the files (HealthCareGovManager_Archive and HealthcareGovManager_AuditLog) to the same destination file (D:\CGI Data\HealthCareGovManager.ndf).

They have to go to separate files or SQL Server will run into a file use issue like you are seeing.

Brian Knight
  • 1,175
  • 2
  • 8
  • 17