Solved

Restore can't access backup file

Posted on 2013-06-27
10
454 Views
Last Modified: 2013-07-03
Hi Experts,

A third party vendor was given a database backup, did some work on it, and returned the updated backup.

Running
restore filelistonly
from disk = N'Somelocaldiskpaththatisreallyasanbecausemachineisvm'

returned
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'Somelocaldiskpaththatisreallyasanbecausemachineisvm'. Operating system error 2(error not found).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Occasionally the Operating system error 3 appeared.

Research shows that this is a permissions issue, that the sql service account doesn't have rights on the file.

But it DID have rights to the file and the folder. In fact, I copied the file into the backup folder for the originating database and still nothing.

Having figured out that it was a rights issue, I copied the backup file to a test server, checked the rights and the restore filelistonly worked.

Meanwhile, the vendor talked me through restoring the backup using the SSMS gui. (I was using SSMS 2008R2 from a test server.) The gui worked!

So, my question is, why would the gui work and the script NOT work?

Regards
  David

PS Further Details
Server SQL 2005 sp3
Windows 2003 sp2
Backup size is around 1.5GB
0
Comment
Question by:David Todd
  • 5
  • 4
10 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39283628
Some questions:

1. You sure you have the correct SQL Service Account?
> Getting the SQL Service Account:
> Start > Run > services.msc
> Locate SQL Server service > Right Click > Properties > Log on tab
> Here you will see what the SQL Service Account is.

2. How do you execute the script? From the same machine? How do you connect?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39283639
Operating system error 3 is "The system cannot find the path specific" and operating system error 2 is "The system cannot find the file specific". If it were a permissions issue you would get an operating system error 5.

You can resolve operating system errors by running the following from cmd...

net helpmsg 2

Open in new window


Based on your error codes you are either typing the path wrong or the file name wrong. From SSMS get to the point where you are about to hit the restore button and then script it to a query window and compare with the statement you have written.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39293272
Do you require further assistance?
0
 
LVL 35

Author Comment

by:David Todd
ID: 39294743
Hi Folks,

Thanks for your interest, but you haven't answered my question.

Given I went over and over the path and filename, copying from Explorer, the chance I got them wrong is pretty small.

Yes I was dealing with the correct service user accounts - I was also cribbing off the backup files from the server, and comparing their rights to the file I was trying to restore.

On some modem systems there is a block attribute/flag on downloaded files, but I couldn't find that to turn it off.

While there is no immediate pressure or constraint to solve this issue currently, restoring a vendors backup is part of what a DBA does, so I want to know for next time. I still have the file, and its relatively small, so I can practice.

Regards
  David
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39295899
Are you not getting operating system error 2 or 3 any more then as the error would be different were it a permissions issue?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 35

Author Comment

by:David Todd
ID: 39295932
Hi

Still getting that error for SQL script.

Regards
  David
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 39295953
Could you go through the gui to do the restore and just before you are about the hit the restore button click on the script button at the top of the window.

Could you then copy and paste both you restore command and the guis restore command so that we can compare.
0
 
LVL 35

Author Closing Comment

by:David Todd
ID: 39296084
Thanks!

Despite all the care in the world I managed to muddle the path and filename enough that my translation of the actual error is file not found.

The original file - as I guessed - was from a more recent version of SQL and when I got the path correct got the more expected error
"The media family on device 'D:\MSSQL2005X64\MEX_PROD\MSSQL.1\MSSQL\Restore\New Folder\MEXDB_After_Import_28062013.bak' is incorrectly formed. SQL Server cannot process this media family."

Reason for the grade of A was the prompt to let the GUI script things out. Doing so showed where I had gone adrift. Many thanks.

Regards
  David
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39296096
No probs. You would not believe the amount of times I do exactly the same thing so your not alone!
0
 
LVL 35

Author Comment

by:David Todd
ID: 39296128
Thanks for the encouragement!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now