Database Restore Failing with Move

database restore failing with move

When restoring, you need to be sure to

• pick a new database name that doesn't already exist (unless you want to overwrite that pre-existing database)

• you tick the Overwrite option in the Options tab page and define valid and new file names for the .mdf and .ldf file so that you don't accidentally overwrite another database on your system:

How to restore database when files are claimed?

You can't restore to C:\Program for multiple reasons. That's not a full path (you seem to have lost the string after the first space in Program Files); the data and log can't both be put in the same file; you don't typically have write access to the root of any drive; and C:\ is not valid in Docker or Linux.

You need the LogicalName, but you should not be using the PhysicalName directly, either in the case where you are restoring to Docker or Linux, or in the case where you are restoring a database alongside an existing copy that you want to keep, or in the case where you are restoring a database to a different instance (which will more than likely have a different data folder structure).

Try:

RESTORE DATABASE US_NATIONAL_COPY 
  FROM DISK = "/var/opt/mssql/backup/us_national_statistics.bak" 
  WITH REPLACE, RECOVERY,
  MOVE "us_national_statistics"      TO "/var/opt/mssql/data/usns_copy.mdf",
  MOVE "us_national_statistics_log"  TO "/var/opt/mssql/data/usns_copy.ldf";

Restoring database back up

I would recommend looking at the names of the files as well as potentially renaming the db just to rule out more options. If it continues occurring that should at least narrow down one more thing.

SQL Server: Database stuck in Restoring state

You need to use the WITH RECOVERY option, with your database RESTORE command, to bring your database online as part of the restore process.

This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.

Your command should look like this,

RESTORE DATABASE MyDatabase
   FROM DISK = 'MyDatabase.bak'
   WITH REPLACE,RECOVERY

You may have more sucess using the restore database wizard in SQL Server Management Studio. This way you can select the specific file locations, the overwrite option, and the WITH Recovery option.

Error restoring database backup

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.

SQL-Server: Error - Exclusive access could not be obtained because the database is in use

I'll assume that if you're restoring a db, you don't care about any existing transactions on that db. Right? If so, this should work for you:

USE master
GO

ALTER DATABASE AdventureWorksDW
SET SINGLE_USER
--This rolls back all uncommitted transactions in the db.
WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE AdventureWorksDW
FROM ...
...
GO

Now, one additional item to be aware. After you set the db into single user mode, someone else may attempt to connect to the db. If they succeed, you won't be able to proceed with your restore. It's a race! My suggestion is to run all three statements at once.

---

Related Topics