Why will C# not Create an SSMS Back-up?

Question

I have the following code to create a back-up of the database.

public void CreateBackup()
{
    if (_database.Database.CanConnect() == false)
    {
        throw new Exception("No connection to the Database could be established.");
    }

    _database.Database.ExecuteSqlCommand($@"BACKUP DATABASE {_databaseSettings.DestinationSettings.SSMSDatabaseName} TO DISK='{_databaseSettings.BackupSettings.BackupFilePath}{Path.DirectorySeparatorChar}{_databaseSettings.DestinationSettings.SSMSDatabaseName}_{DateTime.Now.ToString("dd-MMMM-yyyy HH_mm")}.bak'");
}

The ExecuteSqlCommand will be converted into

BACKUP DATABASE databasename TO DISK='D:\backup\databasename_03-november-2021 15_52.bak'

When I run this SQL command via the SSMS query prompt, it will start creating the back-up with no issue. When I run the above code, which should do essentially the same, it will take the same amount of time as if it is creating a back-up but will not actually create the back-up. I have attempted to run this application on the same computer as the one where the database is hosted. It did not make a difference. I receive no errors from the application.

Could anyone help me make sense of this?

Resources that I've read and checked: https://stackoverflow.com/a/9835597/12175949

Assign the string to a variable first and print it. The most likely possibility is simply that the backup is written, but not where you think it is. You can also check the server's log (find the `ERRORLOG`, or do `sp_readerrorlog`); backups are normally logged there. Also perform more basic checks, like verifying you're connecting to the server you think you are (check the connection string). The one thing you can safely exclude is the possibility that the server is *pretending* to write a backup while not doing so. — Jeroen Mostert, Nov 03 '21 at 15:25
@JeroenMostert Thank you for your advice regarding the sp_readerrorlog. It allowed me to find the issue in conjunction with answer. The database back-up was created on an entirely different drive. The errorlog allowed me to acquire that knowledge. — shinyshark, Nov 04 '21 at 08:30

score 2 · Accepted Answer · edited Nov 04 '21 at 11:08

Since it takes roughly the same amount of time as when you run it in SSMS query prompt, it is safe to assume that it is actually being done. So, it is one of the following cases:

it's located at a place where you do not search for it
it's saved and then removed for some reason
it does not have access rights to the path
the ExecuteSqlCommand throws an exception

Now, to find out what the exact issue (and solution) is in your specific case, you will need to debug your code and see what

{_databaseSettings.BackupSettings.BackupFilePath}{Path.DirectorySeparatorChar}{_databaseSettings.DestinationSettings.SSMSDatabaseName}_{DateTime.Now.ToString("dd-MMMM-yyyy HH_mm")}.bak

evaluates to. Try to find the path. If the path does not exist, then you may want to create the folders and grant access to them for the user which executes the code. If the path is found and the files are generated, then it was just pointing to a different place from the one you expected the save to target. If the path exists, but not the file, then check for privileges. If there is an exception thrown from the line of ExecuteSqlCommand, then the db user being logged in might be missing some privilege.

So, in short, debug your code, see whether it breaks. If so, fix the bug. If not, search for the file, but first, find out what is the actual path the file would be saved into. If the file is not found, find out whether you have the necessary privileges. If so, find out what removes the file.

Edit from OP: This answer provided the right information. Confirming that information was easier to do by reading through the sp_readerrolog as Jeroen Mostert commented.

This was the correct answer, but I got to it by following @Jeroen Mostert's advice. When I checked the sp_errorlog, I found out that it did indeed create a back-up. It created the back-up to an entirely different drive than I had specified. Thank you for your help! — shinyshark, Nov 04 '21 at 08:29

Why will C# not Create an SSMS Back-up?

1 Answers1