ASPNetFAQ.com: What is ASP.NET?

Technology posts on ASP.NET, IIS, Windows (+ a little Linux), Cloud Servers, Hosting, and more!
  • Blog Home
Search the site...

SQL Server restore failure and resolution

Tweet
Share
0 Shares

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!

 

More from my site

  • Orchard: Custom Content in Sub-FoldersOrchard: Custom Content in Sub-Folders
  • Resolving a “There is a duplicate ‘system.web.extensions/scripting/scriptResourceHandler’ section defined” ErrorResolving a “There is a duplicate ‘system.web.extensions/scripting/scriptResourceHandler’ section defined” Error
  • Linking spam sent through shared IIS SMTP server to a userLinking spam sent through shared IIS SMTP server to a user
  • PUT/POST/DELETE Verb Errors On SitePUT/POST/DELETE Verb Errors On Site
  • Implementing Application Initialization on IIS 7.5Implementing Application Initialization on IIS 7.5
Tweet
Share
0 Shares
cytanium, Hosting, OrcsWeb, SQL/Databases, System Administration, Troubleshooting

One comment on “SQL Server restore failure and resolution”

  1. Adam Gorge says:
    September 14, 2012 at 1:02 am

    I too have faced the same problem. I also tried to restore SQL Server Database but the error was different. I tried all possible solutions as mentioned by you but have hard luck to overcome. I was so determined to recover my damaged database. At the end one of my friends suggested me to try Stellar Phoenix SQL Database Recovery Software to repair database. I have successfully run this utility and recovered my corrupt files (mdf & ndf) & finally I got my data, really I am thankful to stellar team for making me happy!!!

Proverbs 19:20

"Get all the advice and instruction you can, so you will be wise the rest of your life."

A Note On WordPress Hosting

Our main focus is of course .NET, but with a mix of Linux, virtualization, and other technologies. But if you're really looking for the best WordPress hosting specifically, read my WordPress host review to save yourself hassle AND money!




Recent Posts

  • What makes good web hosting?
  • jQuery Mobile C# ASP.NET and N5 Networks Software Repository
  • Open Source Bug Tracking Software and the Orchard Project
  • ASP.NET Development with Dreamweaver MX: Visual QuickPro Guide
  • Kendo UI Sample, ASP.NET Ajax Tutorial & More

Tags

ASP.NET Automation centos CMS css cytanium Development/Coding Email gmail Hosting htaccess http https IIS javascript Learning Linux logparser MySQL nginx openssl OrcsWeb performance PowerShell redirect RHEL security server SherWeb smtp SQL/Databases ssl System Administration telnet terminal tip Troubleshooting Ubuntu virtualization Visual Studio web farm web hosting Windows windows server Wordpress

Categories

  • ASP.net development
  • Development/Coding
  • Hosting
  • IIS (Internet Information Services)
  • SQL/Databases
  • System Administration
  • Virtualization
(c) ASPNETFAQ.com