Sql Server 2019 Express Edition database backup file size

Darryl Hoar 141 Reputation points
2023-05-16T16:18:13.5233333+00:00

I have a custom created c# application that backs up the Sql database.

It uses the Backup database databasename to file = 'backup file path and name' command.

It recently has been command timing out and failing to backup. Yes I can modify in the application to change the command timeout value, but I can't do that right now. Running the command in SSMS takes 32 seconds and completes.

I looked at the database mdf file size and its 990,504 KB and the ldf is 150,456 KB, so its not a massive database. The backup file created is 1,989,344KB, which is almost double the size of the mdf file. Since I want to speed up the backup (make it sub 30 seconds to complete) I was looking at reducing the size. What I can't figure out is why the .bak file is 1GB bigger than the mdf size.

any ideas or help greatly appreciated.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,121 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP
    2023-05-16T21:31:29.5133333+00:00

    Are you always backing up to the same file path? If you do this, you are appending a backup to the file every time, unless you specify the INIT option. This could explain why that backup file is so much bigger than the database.

    Another possibility is that you have FILESTREAM tables - the files in these tables are not in the mdf/ldf, but they are in the backup.


  2. AniyaTang-MSFT 12,341 Reputation points Microsoft Vendor
    2023-05-17T07:13:44.0466667+00:00

    Hi @Darryl Hoar

    As Erland said, are you appending the new backup to the same file?

    You can check this link: https://stackoverflow.com/questions/4085402/why-is-sql-server-backup-so-much-larger-than-the-db-files.

    Best regards,

    Aniya