DB backup error: cannot find the path specified.

Question

DB backup error: cannot find the path specified.

junk.mail291276

SSCrazy

Points: 2503
More actions
September 19, 2016 at 11:34 am

#329890

Hello,
I'm trying to backup a database from a command line. I type this:
cd "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn" & sqlcmd.exe -S (localdb)\ProjectsV12 -U SA -P [password]
-i "C:\Users\gshah\SQLBackups\BackupRiskAliveDev.sql"
...and in BackupRiskAliveDev.sql:
BACKUP DATABASE [RiskAliveDev] TO DISK = N'C:\Users\gshah\SQLBackup\backups\RiskAliveDev' WITH NOFORMAT, NOINIT, NAME =
N'RiskAliveDev-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
But I get an error (see the attachment).
The crux of the error says: "Cannot open backup device 'C:\Users\...'. Operating system error 3 (The system cannot find the
path specified)."
...which is strange because the path definitely exists.
Most of my google research tells me this is a networking issue (i.e. it happens when trying to backup to a network drive)
but this is all local to my machine.
Any help would be much appreciated.
September 27, 2016 at 1:02 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jasona.work SSC Guru Points: 50083 More actions · Answer 1

As you're connecting to the SQL using the SA account (generally bad practice) it will be trying to run the backup using the SQL Server service account (you can find what the account is in the SQL Configuration Manager,) and this account likely doesn't have any access to the path you specified.

Either change the backup path to somewhere the SQL service account has access, or grant the service account access to the location you're currently trying to use.

junk.mail291276 SSCrazy Points: 2503 More actions · Answer 2

Thanks for the response jasona,

I tried setting the path to the default backup folder that SQL Server usually uses:

C:\Program Files (x86)\Microsoft SQL Server\120\backups

...but I get an "access denied" error message, which is different from the "cannot find path" message I got before.

(I'm not exactly sure where the service user has access, or where exactly in the "Microsoft SQL Server" folder backups usually go by default--the 120 folder is not the only one).

Also, I'm not sure how to find the service user. I open SQL Server Configuration Manager and didn't see anything corresponding to a service user (please see attachment). In SQL Server Management Studio, the closest thing I found to a service user was NT SERVICE\SQLWriter (see attachment). I played around with the User Mappings for this user, basically giving it all database roles for the database I want to backup (not sure if this is equivalent to giving it access to the specified path--if not, I don't see where that's done).

Sorry for pestering you with all these questions. Database management is not really my field of expertise. Thanks for your help.

jasona.work SSC Guru Points: 50083 More actions · Answer 3

No problem.

First, from this bit in your original post:

(localdb)\ProjectsV12

it looks like you're using (localdb), which I don't have experience with.

As for giving an account access to a path / folder, that's done from outside SQL at the OS level. There's nothing that you can change in SQL that would grant an account access to a file path.

Maybe, presuming I'm correct about the localdb thing, you might want to see what account is being used by the SQLExpress SQL Server instance and try granting that account access to the folder, but no promises.

OK, some quick Google-fu, and it looks like the SQLExpress service account ought to be the one you need to grant permissions to on the filesystem. This TechNet article should put you on the path to changing the permissions.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

You have two different instances on your PC. You have a SQL Server Express instance and a localdb instance.

(localdb)\ProjectsV12 instance is created by SQL Server Data Tools (SSDT) and should not be used by applications. In your configuration manager, the localdb instance isn't running but the Express instance is. Are you sure which instance has the database you want to backup?

For the service accounts that are running your instances (that would need the permissions), Look at the Log On As column in configuration manager. That's the service account. You don't need to mess with SQL Writer at all, just ignore that on your PC.

Don't use a directory in your User path for the backups - just use the default directory since it usually will have the correct permissions and might save you some headaches. Try executing this and see if it gives you the path:

EXEC master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory'

When you get the permissions errors running something in SSMS, try getting out of SSMS, then reopen it but open it by right clicking and do a Run As Administrator.

Sue

junk.mail291276 SSCrazy Points: 2503 More actions · Answer 5

According to that query, the default path is c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup

According to SQL Config Manager, the service user for .\SQLEXPRESS is NT AUTHORITY\NETWORK SERVICE.

So I set the permission on the above path to full control for the NETWORK SERVICE USER. Then I changed the SQL command to:

BACKUP DATABASE [RiskAlive] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup' WITH NOFORMAT, NOINIT, NAME = N'RiskAlive-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Note the new backup path and the new database I'm trying to back up (RiskAlive instead of RiskAliveDev). RiskAlive is an earlier version of RiskAliveDev that's stored under .\SQLEXPRESS. So NETWORK SERVICE, running under .\SQLEXPRESS, with permissions on the above path, should have access to both the RiskAlive database and the backup folder.

But here's the error I get:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn>cd "C:\Program Files

(x86)\Microsoft SQL Server\110\Tools\Binn" & sqlcmd.exe -S .\SQLEXPRESS -E -i "C

:\SQLBackups\BackupRiskAliveDev.sql"

Msg 3201, Level 16, State 1, Server ACMCAL-LT160\SQLEXPRESS, Line 1

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPR

ESS\MSSQL\Backup'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Server ACMCAL-LT160\SQLEXPRESS, Line 1

BACKUP DATABASE is terminating abnormally.

Note that I'm no longer using SA, I'm using the -E flag (which, if I'm not mistaken, means my Windows credentials). Logging in as SA was failing. I would think that in order to get the above error message, login would have to be successful, no?

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Yes the login was successful as the backup began and then "BACKUP DATABASE is terminating abnormally". So the login and the start of the backup. I could be a UAC issue at this point.

Sue