Question Can my database copying method be more efficient?

TB007

Member
Joined
Aug 13, 2022
Messages
24
Programming Experience
Beginner
Prensently I'm using the below approach to copy multiple database files (SQLite) from one location to another and overwrite them if the database/databases already exists:

C#:
        string sourceDir = @"D:\test\from\";
        string backupDir = @"D:\test\to\";

        string[] databaseFiles = Directory.GetFiles(sourceDir, "*.db");

        foreach (string databaseFile in databaseFiles)
        {
            string fileName = databaseFile.Substring(sourceDir.Length);
            
            CopyDatabase(Path.Combine(sourceDir, fileName), Path.Combine(backupDir, fileName));
        }

The CopyDatabase method is as under:
C#:
public static void CopyDatabase(string sourceFile, string destFile)
    {
        using (SQLiteConnection source = new SQLiteConnection(String.Format("Data Source = {0}", sourceFile)))
            using (SQLiteConnection destination = new SQLiteConnection(String.Format("Data Source = {0}", destFile)))
        {
            source.Open();
            destination.Open();
            source.BackupDatabase(destination, "main", "main", -1, null, -1);
        }
    }

Can this method be made more efficient and/or less crash/error proof ?

Are there other/better ways to do this ?
 
C#:
var sourceFolderPath = @"D:\test\from\";
var backupFolderPath = @"D:\test\to\";

var sourceFilePaths = Directory.GetFiles(sourceFolderPath, "*.db");

foreach (var sourceFilePath in sourceFilePaths)
{
    var fileName = Path.GetFileName(sourceFilePath)

    CopyDatabase(sourceFilePath, Path.Combine(backupFolderPath, fileName));
}
 
If you don't need the source and destination databases concurrently usable while you are copying, then I suggest using File.CopyFile() instead of SQLite.BackupDatabase(). My reasoning is that it is better to let the OS just do a pure I/O operation of simply copying files, vs. the overhead of the CPU and I/O of backup which needs to scan the source, resolve or apply locks, send rows to the destination, for each table in the database.

Recall that SQLite is supposed to be a single file database.
 
Yes, SD means to just perform an OS level file copy operation liek you would any other file, as SQLite is a file-based database
 
I have the database files hidden in the backupFolderPath, so the copy method throws an exception. How to get around that efficiently ?
 
What do you mean "hidden"? As in the "get properties, tick hidden, OK" route? I've never heard that preventing File.Copy from copying it; what exception is being thrown exactly?
 
What do you mean "hidden"? As in the "get properties, tick hidden, OK" route?
Yes, exactly that and I'm getting System.UnauthorizedAccessException: Access to the path 'D:\test\to\test.db' is denied.
 
Mmm.. Hiding a file won't cause UnauthorizedAccessException to throw, but running a program as user X when X has no access to the location will

Unless that was what you meant by "hidden".. FYI when you say "hidden", it will make a fairly knowledgeable windows user think of this:

1672755759181.png


If this is what you mean by "hidden":

1672755928917.png


i.e. security and file permissions, then please avoid calling that "hidden", to limit confusion with actual "Hidden"

--

To resolve the problem of the UAE, give the user running the program performing the copy, permissions to access the nominated location. Remember that the user running the program isn't necessarily you; it could be any other user on the machine or domain, depending on how the software has been configured to impersonate
 
Even after giving the user permissions to access the nominated location I'm still getting the same error...

1672760399667.png
 
After editing the permissions now there are two Users, one with Full control and other with Read & execute

1672761293371.png
 
First step to try is to use File Explorer and try copying from your old location to the backup location. If that succeeds, let the person trying to do the backup also perform the same operation.

As big note. Recall that I prefaced my post #5 with:
If you don't need the source and destination databases concurrently usable while you are copying

Although not trying to copy SQLite files, I have seen the "Access to path denied" errors while trying to copy spreadsheets into another folder while the destination spreadsheet was being held open by another user. I would have expected something like an error saying that the file was locked by another user. Anyway, the point is: Make sure that the destination file is not being held open by another application.
 
Back
Top Bottom