A friend on mine had a recent issue where the restore of a SQL Server backup file – taken on one server and being restored to a different one – wouldn’t complete. It would continually throw an error of:

Server was unable to process request. —> File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TheDatabase_log.ldf’ is claimed by ‘YAF_log'(2) and ‘ftrow_YafSearch’.

That made no sense and the names in the error message (I changed the DB name to “TheDatabase”) didn’t match (before my change I mean :>) anything in reality. The database didn’t already exist, and those other two file names, which the error states have some sort of “claim” on the log file, didn’t match anything in the destination system.

After a lot of troubleshooting with no luck, he stumbled across some information online that helped him get past it. I asked him to summarize so I could post here in case it helps someone else – or for reference for myself if I ever run across the issue.

His summary to me:

The issue seems to happen when there is a secondary database file (NDF) in the BAK file. The SQL restore doesn’t seem to like NDF files if the file does not already exist.

If this happens with a dedicated server you just create the files first (step 4), then restore, and you’re good to go. With a shared hosting account you have to do a bit more.

Steps to fix:

1. Download and install SQL Server Express to a local computer, if you do not have access to SQL Server already. You will need file level access to the SQL Server so you cannot do this connected to a shared hosting SQL Server instance with SSMS.
2. Connect to your local SQL Server instance.
3. Create a new, blank database with the same name as the database you need to restore.
4. Open a SQL query and run the following command, replacing ‘path\file.bak’ with the full path to your BAK file, INCLUDING the double single ticks (‘ ‘) surrounding the path. This will create all the necessary files needed to restore the database.

RESTORE FILELISTONLY FROM DISK = ‘path\file.bak’

5. Restore the database to the local server.

NOTE: If you get stuck in “DBname (Restoring…)” mode after the restore run the following command (WITH RECOVERY must be on a separate line):

RESTORE DATABASE DBname
WITH RECOVERY

6. You will now need to merge the NDF(s) with the MDF. For each NDF file run the following command, replacing the names appropriately:

USE DBname
DBCC shrinkfile(‘file.ndf’,emptyfile)
ALTER DATABASE DBname
REMOVE FILE logical_file_name;
GO

You can get the NDF file name by navigating to your SQL Data folder or running this query:

SELECT name, physical_name AS current_file_location
FROM sys.master_files

You can get the logical_file_name from the Files section or the name column from the above command.

7. Backup the database from the local SQL Server instance and restore it to the shared hosting SQL Server instance – it should work fine this time!

Hopefully this is helpful to someone else in the same situation. Happy hosting!