How to backup a database in WPF with C# and SQL Server?

Question

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?

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. — jdweng, Apr 19 '21 at 08:28
@AVEbrahimi this will be a big problem. ( I would like to have the path as parameter not restricted ) — , Apr 19 '21 at 08:30
Does your SQL-Server have permission to access the folder? Can it be, that it has less permission, then you. — Rekshino, Apr 19 '21 at 08:40
The user the SQL Server service runs into can be looked under "Services -> SQL Server -> Properties -> Log on" — Cleptus, Apr 19 '21 at 08:48
Usually you can see it in task list and/or service properties. — Rekshino, Apr 19 '21 at 08:49
@Rekshino , No I have already tried this but I got the same error — , Apr 19 '21 at 09:00
Try opening the database with Notepad using a File Explorer. A few things could be happening 1) The path doesn't exist 2) You do not have permission to write to the folder 3) The file already exists and is opened so you cannot write to location. 4) The file has to be o the same machine physically as the sql Server software. The server may be on a different machine or your user folder may be on a different machine. — jdweng, Apr 19 '21 at 09:14
@jdweng , I think that the MDF and LDF database files are read-only — , Apr 19 '21 at 09:20
When a MDF file is attached to a SQL Server the server owns the file and does not allow access to the file by other applications. If the backup is not done correctly the permissions get messed up. Then you need an admin to restore the permission of the file. If you are running you code from VS you do not automatically run as an Admin. To run code as Admin you need to right click the VS shortcut and select Run As Admin. — jdweng, Apr 19 '21 at 10:06
Yes, this is correct but when the application is opened with VS 2019, what about if the app is running on the client machine? — , Apr 19 '21 at 10:08

PMF · Answer 1 · 2021-04-19T09:04:03.663

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
    }

edited Apr 19 '21 at 09:04

answered Apr 19 '21 at 08:49

PMF

14,535
3
23
49

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 . – Apr 19 '21 at 08:52
Sure. That's why I said that it needs to be copied away to the folder selected by the user after the export. – PMF Apr 19 '21 at 08:53
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. – Apr 19 '21 at 08:55
See my update above. (This is untested, but you should get the idea) – PMF Apr 19 '21 at 09:04
Yes, I've understood what do you mean, it is a good idea. But unfortunately I got the same error. – Apr 19 '21 at 09:11
Is this SQL server instance local? Or do you connect to a remote server? – PMF Apr 19 '21 at 09:12
For now, it's an instance local, but in the future, I will use a remote server. – Apr 19 '21 at 09:13
You need to find a path the server can access. Temp usually worked for me, but that might depend on several other settings as well. Check with the management studio to find a folder you can backup to. Also note that this will not work with remote servers. It's not possible to backup a database to a different computer. – PMF Apr 19 '21 at 09:15
So it can't be a solution for me ( if it's not possible to back up a database to a different computer ), did you know why the Temp folder does not work for me? Because the file is under C local drive that can't be accessed by SSMS. – Apr 19 '21 at 09:19
No, I don't know that. Another thing you could try is the default backup folder for SQL (C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup), but this one might not be accessible for a user :-( – PMF Apr 19 '21 at 09:24

score 0 · Answer 2 · edited Apr 19 '21 at 09:16

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

Yes, I mentioned that in my post. They wanted to see the code. https://converter.telerik.com/ — Linux5021, Apr 22 '21 at 10:08

score 0 · Accepted Answer · 2021-04-21T10:30:56.233

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

edited Apr 21 '21 at 10:30

answered Apr 19 '21 at 09:44

How to backup a database in WPF with C# and SQL Server?

3 Answers3