-
Sep 17th, 2020, 06:46 PM
#1
Thread Starter
Lively Member
Running code against Sqlserver worked fine first time but now 'Access denied'
I am trying to backup my DB using this code First time I got it to run it worked well and My DB was backed up
Since then I am getting 'Access Denied' errors at the executeNonQuery line . I deleted the old backup in case it just didn't like overwriting it but that didn't help
The exact error is ---
"Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS17\MSSQL\Backup'. Operating system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally."
any advice would be appreciated
Code:
Dim sqlConnectionString As String = $"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog={DBName};Data Source={Server}"
'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BankAccounts;Data Source=MAIN\SQLEXPRESS17
Dim BackupLocation As String
BackupLocation = GetAllTextBeforeLastOccuranceof(DataLocation, "\") & "Backup"
Dim conn As New SqlConnection(sqlConnectionString)
conn.Open()
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = $"BACKUP DATABASE BankAccounts TO DISK='{BackupLocation}'"
cmd.Connection = conn
cmd.ExecuteNonQuery()
conn.Close()
Last edited by mpooley; Sep 17th, 2020 at 06:47 PM.
Reason: spelling
-
Sep 17th, 2020, 07:49 PM
#2
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
Did you perhaps run your application as an administrator the first time?
-
Sep 18th, 2020, 01:17 AM
#3
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
Backup (write-permissions) in "Program Files"?
Good lord......
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Sep 18th, 2020, 06:35 AM
#4
Thread Starter
Lively Member
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
@Zvoni
Would be helpful if you could be a bit less facetious.
-
Sep 18th, 2020, 07:21 AM
#5
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
Originally Posted by mpooley
@Zvoni
Would be helpful if you could be a bit less facetious.
OK, writing files to anywhere in the Program Files is a bad idea... MS has increasingly locked down that folder over the years to prevent users & apps from shooting themselves in the foot. Ideally, your backups should be more accessable anyways and be relocated to a clean directory all its own, not one part of the applicaiton's install path.
When ever I set up a SQL Server, I create a D: (or what ever drive is NOT C \SQLBackups\{DB Name} and store the backups for databsses in individual folders. SO the Northwind database gets backed up to D:\SQLBackups\Northwind\*.bak
-tg
-
Sep 18th, 2020, 10:10 AM
#6
Thread Starter
Lively Member
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
Thankyou!
I was only doing this as a test as it's going to be deployed elsewhere, I'd assumed I would have Access but I'll change it to one of the user folders
-
Sep 18th, 2020, 06:58 AM
#7
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
You need to add a file name to the backup command.. It looks like you are sending a folder path without a file name
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Sep 19th, 2020, 09:33 AM
#8
Thread Starter
Lively Member
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
Originally Posted by GaryMazzone
You need to add a file name to the backup command.. It looks like you are sending a folder path without a file name
You were right about that! thanks I hadn't noticed.
still had access problems but sorted as far as testing is concerned
-
Sep 18th, 2020, 10:37 AM
#9
Thread Starter
Lively Member
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
I changed to my user folder to Test it but I got the same error?
-
Sep 18th, 2020, 11:31 AM
#10
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
Couple things:
1) Why would you have a user folder on the server?
2) Even if you did, you're not the "user" on the server, the service is... it doesn't have the same context, so your user folder wouldn't be visible to the service. That's why I use the path I use... it's not tied to any user.
3) Just to be sure, you are saving it to a path that EXISTS and is LEGAL on the SERVER, and NOT on your machine, right? Because that's a common mistake I see people make with running the backup process... thinking that it will be on their local system somehow and will give it a folder on their machine, but doesn't exist on the server.
-tg
-
Sep 18th, 2020, 12:22 PM
#11
Thread Starter
Lively Member
Re: Running code against Sqlserver worked fine first time but now 'Access denied'
Sorry I didn't explain properly the application will use a local copy of sqlserverExpress and I am testing on Pc with sqlserverExpress installed
The path I'm testing does exist
thanks
Mike
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|