I have a SQL Server 2008 r2 database that is currently 56 GBs. When I run the following code I get a bak file that is 93 GBs. What could be causing this?

    BACKUP DATABASE [CommerceCenter]
TO DISK = 'N:\MSSQL\DATA\CC\CC_backup_tues.bak'

If I use SSMS to run the backup to the same location with compression the bak file is only 8 GBs.

  • 2
    Have you written multiple backups to the same file? You haven't specified WITH INIT in your command. See MSDN. Oct 17 '16 at 12:36

Most likely, it's multiple backups inside the same file. To see it:

FROM DISK = N'N:\MSSQL\DATA\CC\CC_backup_tues.bak' ;

If you see multiple backups in there, you have a couple of different options:

  • Specify a different file name per backup (I'm a huge fan of this - use date-based file names rather than days), or
  • Use the WITH INIT command to overwrite the last backup - this can be a bad idea if you ever need to go back more than a week
  • Yes it had multiple files in it. For this database any backup over a week is useless, so I will go with the WITH INIT. Thanks. Oct 17 '16 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.