asked on

Cannot open backup device on sql server 2014 backup

I have a sql server 2014 database. I just migrated the data from 2008.

I have 4 databases. I have created a .bak copy of every one of them.

So far with 2 of them, I am able to RESTORE from the .bak copy I created.
Two others fail. Below is an example of my script. It is identical for each database, with just the name change.

RESTORE DATABASE [REEKill2012] FROM  DISK = N'C:\DBFBackups\Hourly\Ree2005Hourly.bak' WITH  FILE = 1,  MOVE N'Ree2005' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\REEKill2012.mdf',  MOVE N'Ree2005_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\REEKill2012_1.ldf',  NOUNLOAD,  STATS = 5

Open in new window

when I run it on two of the databases, most important ones of course, I get this error:

Cannot open backup device 'C:\DBFBackups\Hourly\Ree2005Hourly.bak'. Operating system error 2(The system cannot find the file specified.).

In the screenshot, you can see that the backup exists, and the file size is there too to show there is data.

What can be wrong?

thanks!

Starr Duskk

ASKER

Also... Here is the original script used to restore from the sql server 2008 backup. (I changed the name of the restore for this example, so I wouldn't accidentally wipe out my life database.)

RESTORE DATABASE [REE2005Kill] FROM  
DISK = N'C:\DBFBackups\From 2008\REEfinalFrom2008Works\Ree2005_Hourly.bak' 
WITH  FILE = 2,  MOVE N'REE2005_dat' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\REE2005Kill.mdf', 
 MOVE N'REE2005_log' 
 TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\REE2005Kill_1.ldf',
   NOUNLOAD,  STATS = 5

Open in new window

I copied that and changed it to the new backup set, and changed the name:

RESTORE DATABASE [REE2005Kill] FROM  DISK = N' C:\DBFBackups\Hourly\Ree2005Hourly.WITH  FILE = 2,  MOVE N'REE2005_dat' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\REE2005Kill.mdf',  MOVE N'REE2005_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\REE2005Kill_1.ldf',  NOUNLOAD,  STATS = 5

Open in new window

I used the Task backup option to create the backup. And whether I run it from there or this script, it still restores with the error. This one is going to the hourly folder, but I have the daily failing as well.

BACKUP DATABASE [REE2005] TO  DISK = N'C:\DBFBackups\Hourly\ Ree2005Hourly.bak' WITH NOFORMAT, INIT,  NAME = N'REE2005-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Open in new window

What could be the problem? thanks!

ASKER CERTIFIED SOLUTION

Deepak Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Starr Duskk

ASKER

Your suggestion helped me track it down.

On creation of the backup, my script had a space before the file name, after the slash in the path.

thanks!