Error restoring database backup

Question

I am getting an error using SQL Server 2012 when restoring a backup made with a previous version (SQL Server 2008). I actually have several backup files of the same database (taken at different times in the past). The newest ones are restored without any problems; however, one of them gives the following error:

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\MYDB_ABC.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.SmoExtended)

This is a x64 machine, and my database file(s) are in this location: c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL.

I do not understand why it tries to restore on MSSQL.1 and not MSSQL11.MSSQLSERVER.

Answer 1

Sounds like the backup was taken on a machine whose paths do not match yours. Try performing the backup using T-SQL instead of the UI. Also make sure that the paths you're specifying actually exist, that the SQL Server service account has the ability to write there, and that there aren't already copies of these files there.

RESTORE DATABASE MYDB_ABC 
  FROM DISK = 'C:\path\file.bak'
  WITH MOVE 
            /* this is the logical data file name, not the path: */
            'mydb_logical_data_filename' 

         TO 
            /* this is the physical data file path: */
            'c:\valid_data_path\MYDB_ABC.mdf',

       MOVE 
            /* this is the logical log file name, not the path: */
            'mydb_logical_log_filename' 

         TO 
            /* this is the physical log file path: */
            'c:\valid_log_path\MYDB_ABC.ldf';

From the documentation, Restore a Database to a New Location (SQL Server):

Answer 2

If you're doing this with C#, and the physical paths are not the same, you need to use RelocateFiles, as one answer here also mentioned.

For most cases, the below code will work, assuming:

1. You're just restoring a backup of a database from elsewhere, otherwise meant to be identical. For example, a copy of production to a local Db.

2. You aren't using an atypical database layout, for example one where the rows files are spread across multiple files on multiple disks.

In addition, the below is only necessary on first restore. Once a single successful restore occurs, the below file mapping will already be setup for you in Sql Server. But, the first time - restoring a bak file to a blank db - you basically have to say, "Yes, use the Db files in their default, local locations, instead of freaking out" and you need to tell it to keep things in the same place by, oddly enough, telling it to relocate them:

var dbDataFile = db.FileGroups[0].Files[0];
restore.RelocateFiles.Add(new RelocateFile(dbDataFile.Name, dbDataFile.FileName));
var dbLogFile = db.LogFiles[0];
restore.RelocateFiles.Add(new RelocateFile(dbLogFile.Name, dbLogFile.FileName));

To better clarify what a typical case would be, and how you'd do the restore, here's the full code for a typical restore of a .bak file to a local machine:

var smoServer = new Microsoft.SqlServer.Management.Smo.Server(
    new Microsoft.SqlServer.Management.Common.ServerConnection(sqlServerInstanceName));

var db = smoServer.Databases[dbName];
if (db == null)
{
    db = new Microsoft.SqlServer.Management.Smo.Database(smoServer, dbName);
    db.Create();
}

restore.Devices.AddDevice(backupFileName, DeviceType.File);
restore.Database = dbName;
restore.FileNumber = 0;
restore.Action = RestoreActionType.Database;
restore.ReplaceDatabase = true;

var dbDataFile = db.FileGroups[0].Files[0];
restore.RelocateFiles.Add(new RelocateFile(dbDataFile.Name, dbDataFile.FileName));
var dbLogFile = db.LogFiles[0];
restore.RelocateFiles.Add(new RelocateFile(dbLogFile.Name, dbLogFile.FileName));

restore.SqlRestore(smoServer);

db.SetOnline();
smoServer.Refresh();
db.Refresh();

This code will work whether you've manually restored this Db before, created one manually with just the name and no data, or done nothing - started with a totally blank machine, with just Sql Server installed and no databases whatsoever.

Answer 3

I had the same problem, and this fixed it without any C# code:

USE [master]
ALTER DATABASE [MyDb] 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [MyDb] 
FROM  DISK = N'D:\backups\mydb.bak' 
WITH  FILE = 1,  
MOVE N'MyDb' TO N''c:\valid_data_path\MyDb.mdf',  
MOVE N'MyDb_log' TO N'\valid_log_path\MyDb.ldf',  
NOUNLOAD,  
REPLACE,  
STATS = 5
ALTER DATABASE [MyDb] SET MULTI_USER
GO

Answer 4

This usually happens, when you are using one MSSQL Studio for backup (connected to old server) and restore (connected to new one). Just make sure you are executing the restore on the correct server. Either check the server name and IP in the left pane in UI or dou

Answer 5

You should remove these lines from your script.

CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'StudentManagement', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\StudentManagement.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'StudentManagement_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\StudentManagement_log.ldf' , SIZE = 5696KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [StudentManagement] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [StudentManagement].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [StudentManagement] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [StudentManagement] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [StudentManagement] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [StudentManagement] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [StudentManagement] SET ARITHABORT OFF 
GO
ALTER DATABASE [StudentManagement] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [StudentManagement] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [StudentManagement] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [StudentManagement] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [StudentManagement] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [StudentManagement] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [StudentManagement] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [StudentManagement] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [StudentManagement] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [StudentManagement] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [StudentManagement] SET  DISABLE_BROKER 
GO
ALTER DATABASE [StudentManagement] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [StudentManagement] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [StudentManagement] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [StudentManagement] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [StudentManagement] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [StudentManagement] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [StudentManagement] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [StudentManagement] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [StudentManagement] SET  MULTI_USER 
GO
ALTER DATABASE [StudentManagement] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [StudentManagement] SET DB_CHAINING OFF 
GO
ALTER DATABASE [StudentManagement] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [StudentManagement] SET TARGET_RECOVERY_TIME = 0 SECONDS 

Answer 6

Just in case this is useful for someone working directly with Powershell (using the SMO library), in this particular case there were secondary data files as well. I enhanced the script a little by killing any open processes and then doing the restore.

Import-module SQLPS
$svr = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "server name";
$svr.KillAllProcesses("database_name");
$RelocateData1 = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" ("primary_logical_name","C:\...\SQLDATA\DATA\database_name.mdf")
$RelocateData2 = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" ("secondary_logical_name_2","C:\...\SQLDATA\DATA\secondary_file_2.mdf")
$RelocateData3 = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" ("secondary_logical_name_3","C:\...\SQLDATA\DATA\secondary_file_3.mdf")
$RelocateLog = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" ("database_name_log","C:\...\SQLDATA\LOGS\database_name_log.ldf")
Restore-SqlDatabase -ServerInstance "server-name" -Database "database_name" -BackupFile "\\BACKUPS\\database_name.bak" -RelocateFile @($RelocateData1, $RelocateData2, $RelocateData3, $RelocateLog) -ReplaceDatabase

Answer 7

When restoring, under Files, check 'Relocate all files to folder'

Answer 8

As has already been said a few times, restoring a backup where the new and old paths for the mdf and ldf files don't match can cause this error. There are several good examples here already of how to deal with that with SQL, none of them however worked for me until I realised that in my case I needed to include the '.mdf' and '.ldf' extensions in the from part of the 'MOVE' statement, e.g.:

RESTORE DATABASE [SomeDB] 
FROM DISK = N'D:\SomeDB.bak' 
WITH MOVE N'SomeDB.mdf' TO N'D:\SQL Server\MSSQL12.MyInstance\MSSQL\DATA\SomeDB.mdf', 
MOVE N'SomeDb_log.ldf' TO N'D:\SQL Server\MSSQL12.MyInstance\MSSQL\DATA\SomeDB_log.ldf'

Hope that saves someone some pain, I could not understand why SQL was suggesting I needed to use the WITH MOVE option when I already was doing so.

Answer 9

Try restarting the SQL Service. Worked for me.

Answer 10

There is some version issue in this. You can migrate your database to 2012 by 2 another methods:-

1) take the database offline > copy the .mdf and .ldf files to the target server data folder and attach the database. refer this:- https://dba.stackexchange.com/questions/30440/how-do-i-attach-a-database-in-sql-server

2) Create script of the whole database with schema & Data and run it on the target server(very slow process takes time). refer this:- Generate script in SQL Server Management Studio

Answer 11

Please try to uncheck the “Tail-Log Backup” option on the Options page of the Restore Database dialog

Answer 12

Please change the .mdf file path. Just create a folder in any drive, ie - in "D" drive, just create a folder with custom name (dbase) and point the path to the new folder, mssql will automatically create the files.

"C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\MYDB_ABC.MDF" to "D:\dbase\MYDB_ABC.MDF"

Answer 13

I have managed to do this from code. This was not enough

Restore bkp = new Restore();
bkp.PercentCompleteNotification = 1;
bkp.Action = RestoreActionType.Database;
bkp.Database = sDatabase;
bkp.ReplaceDatabase = true;

The RelocateFiles property must be filled with the names and paths of the files to be relocated. For each file you must specify the name of the file and the new physical path. So what I did was looking at the PrimaryFilePath of the database I was restoring to, and use that as the physical location. Something like this:

if (!string.IsNullOrEmpty(sDataFileName) && !File.Exists(sDataFileName))
{
   if (originaldb != null)
   {
      if (string.Compare(Path.GetDirectoryName(sDataFileName), originaldb.PrimaryFilePath, true) != 0)
      {
         string sPhysicalDataFileName = Path.Combine(originaldb.PrimaryFilePath, sDatabase + ".MDF");
         bkp.RelocateFiles.Add(new RelocateFile(sLogicalDataFileName, sPhysicalDataFileName));
      }                  
   }
}

Same for the log file.

Answer 14

The backup stores the original location of the database files and, by default, attempts to restore to the same location. Since your new server installation is in new directories and, presumably, the old directories no longer exist, you need to alter the directories from the defaults to match the location you wish it to use.

Depending on how you are restoring the database, the way to do this will differ. If you're using SSMS, look through the tabs and lists until you find the list of files and their associated disk locations - you can then edit those locations before restoring.