question

56581206 avatar image
0 Votes"
56581206 asked SeeyaXi-msft commented

Inconsitent behaviour of BACKUP (Transact-SQL) from program on different machines

I need a little help as this is driving me crazy and was not able to identify the root cause.

I've developed a program that uses and SQLExpress to store some data.
I've added a function to perform a backup of the DB via T-SQL:

        var backupCommand = "BACKUP DATABASE @databaseName TO DISK = @backupFilePath WITH FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD";

         try
         {
             using (var conn = new SqlConnection(SQLServerSelectorService.ConnectionString))
             {
                 using (var cmd = new SqlCommand(backupCommand, conn))
                 {
                     conn.Open();
                     cmd.Parameters.AddWithValue("@databaseName", conn.Database);
                     cmd.Parameters.AddWithValue("@backupFilePath", _destf);
                     cmd.ExecuteNonQuery();
                     conn.Close();
                 }
             }
        }


The _destf is could be both in a new folder or in the user documents folder.

What is driving me mad is the fact I have a different behaviour on different machines even if, in theory, the setup is the same.
1) development machine -> works well
2) test machine -> always issuse - File system access error.

The setup of the test machine is:
a) clean setup of W10 Home or Pro
b) SQL Server Express setup with all default values
c) SQL Server Tools setup with default values

On the program works perfectly, SQL Server is accessed, data is read and wrote, everything fine BUT it does not backup.

The issue is there even if I launch the app as Administrator.

If I add to the folder the user SQLServer2005SQLBrowserUser$ the issue is still there.

As workaround I've discovered that in the Folder Security on the file system I add the group "Users" with basic modify, read, write permissions it works.

Can you help me understand what's going on? if/how to solve it?

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @56581206 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered TomPhillips-1744 commented

The backup is performed under the security context of the SQL Server service account, so you need to make sure that this account has write permission on the folder.

You view the service command from SQL Server Configuration Manager.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I've checked and, as is a full default installation, the use is the MSSQL$SQLEXPRESS user

The folder I'm trying to write to is a sub folder of the user "Documents", with no changes in the rights.

This is why is confusing to see two different behaviours on two pc

0 Votes 0 ·

The service account does not have access to the User folder. You need to redirect your backup to a location which the service account has access too.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft commented

Hi @56581206 ,

First of all, your script should be fine, because development machine -> works well.
Backing up files to a specified folder is a write operation to that folder. So of course the folder needs the appropriate permissions. If the Windows account (Users) you are using does not have write access to the folder, backing up the database to that folder will of course cause problems.

Best regards,
Seeya


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi,
thank you for your info, as wrote above is quite strange as the folder I'm trying to write to is a sub folder of the user "Documents", with no changes in the rights.

This is why is confusing to see two different behaviours on two pc.

Is there any way to specify the user to perform the backup with?

0 Votes 0 ·

Is there any way to specify the user to perform the backup with?

No. It is always the service account of the SQL Server instance.

As Tom suggests C:\Users\ is not the place where you write the backups. (Well, you could write to C:\Users\MSSQL$EXPRESS, but that would still be sort of odd thing to do.)

0 Votes 0 ·

Hi @56581206 ,

Agree with all.
You need to redirect your backup to a location which the service account has access too. Do not back up in the User folder (including its subfolders).

Best regards,
Seeya

0 Votes 0 ·