0

I'm trying to back up my database using this C# code How to backup and restore SQL Server in WPF with C# and Entity Framework 

private static void CreateBackup(string databaseName, string backupFilePath)
    {
        GlobalConfig gb = new GlobalConfig();
        string connectionString = gb.GetConnectionString();
        backupFilePath = backupFilePath + "\\" + databaseName + ".bak";
        backupFilePath = @""+backupFilePath;
        var backupCommand = "BACKUP DATABASE @databaseName TO DISK = @backupFilePath";
        using (var conn = new SqlConnection(connectionString))
        using (var cmd = new SqlCommand(backupCommand, conn))
        {
            conn.Open();
            cmd.Parameters.AddWithValue("@databaseName", databaseName);
            cmd.Parameters.AddWithValue("@backupFilePath", backupFilePath);
            cmd.ExecuteNonQuery();
        }
    }

CreateBackup("Test","C:\Desktop\Backup\\Test.bak");

But I got this error :

Cannot open backup device 'C:\Desktop\Backup\Test.bak'. Operating system error 5(Access is denied.).

What I'm doing wrong with this code?

How can I fix this error?

Improve this question
15
  • 1
    Is your process allowed to access C:\Desktop\Bakup? Commented Apr 19, 2021 at 8:26
  • Yes I can access this folder Commented Apr 19, 2021 at 8:27
  • C: access is limited in recent Windows versions. Commented Apr 19, 2021 at 8:28
  • The path for file should include c:\users\my name\". Open a file explorer and find file using "c"\users" to get full path of the filename. Commented Apr 19, 2021 at 8:28
  • 1
    The user the SQL Server service runs into can be looked under "Services -> SQL Server -> Properties -> Log on" Commented Apr 19, 2021 at 8:48

3 Answers 3

Reset to default
1

The SQL Server process typically does not run with the permissions of the currently logged in user, therefore it cannot access the users desktop (nor most of the folders of the user or any network folders). It is not possible to freely choose the folder for the backup.

Your best solution is to export to a folder where the server process has access to (i.e. the system temp folder) and then copy the backup from there to wherever you want it.

private static void CreateBackup(string databaseName, string backupFilePath)
    {
        GlobalConfig gb = new GlobalConfig();
        string connectionString = gb.GetConnectionString();
        // Create the backup in the temp directory (the server should have access there)
        var backup = Path.Combine(Path.GetTempPath(), "TemporaryBackup.bak");
        var backupCommand = "BACKUP DATABASE @databaseName TO DISK = @backup";
        using (var conn = new SqlConnection(connectionString))
        using (var cmd = new SqlCommand(backupCommand, conn))
        {
            conn.Open();
            cmd.Parameters.AddWithValue("@databaseName", databaseName);
            cmd.Parameters.AddWithValue("@backup", backup);
            cmd.ExecuteNonQuery();
        }

        File.Copy(backup, backupFilePath); // Copy file to final location
    }
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Temp folder files can be deleted when restarting the PC or when delete caches , and in this case , the user ( client ) will lose the backup database .
Sure. That's why I said that it needs to be copied away to the folder selected by the user after the export.
Yes, but not me, the user should have the choice to select a folder ( maybe as you said I should exclude system folders from the selection can be a solution ) I didn't have any idea if this can be reached or not.
See my update above. (This is untested, but you should get the idea)
Yes, I've understood what do you mean, it is a good idea. But unfortunately I got the same error.
|
0

I have successfully backed up SQL Server databases using Microsoft.SqlServer.Management.Smo.Backup, might want to try that. Mine was in VB years ago but it is still working today. Here is the VB code if it helps:

Dim mySourceServer As New Server(My.Settings.SQLInstance)
Dim bkpDBFullWithCompression As New Backup()
' Specify whether you want to back up database or files or log 
Me.Cursor = Cursors.WaitCursor()

bkpDBFullWithCompression.Action = BackupActionType.Database
' Specify the name of the database to back up 
bkpDBFullWithCompression.Database = _sBackupDatabaseName
bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.[On]
bkpDBFullWithCompression.Devices.AddDevice(_sBackupFilePath, DeviceType.File)
bkpDBFullWithCompression.BackupSetName = _sBackupDatabaseName + " database Backup - Compressed"
bkpDBFullWithCompression.BackupSetDescription = _sBackupDatabaseName + " database - Full Backup"
Try
    bkpDBFullWithCompression.SqlBackup(mySourceServer)
Catch ex As SmoException
    blSuccess = False '
    Me.Cursor = Cursors.Default
End Try
Improve this answer

3 Comments

Yes, this can be a solution but this VB code.
Yes, I mentioned that in my post. They wanted to see the code. converter.telerik.com
I got an error of converting
0

This solution ( Check Local System account instead of This account ) worked for me, but I didn't have any idea if it's a good solution for security or no.

You can find the LogOn tab setting under this :

Services -> SQL Server -> Properties -> Log on

enter image description here

Improve this answer

Comments