The backup cannot be performed because 'COMPRESSION' was requested after the media was formatted with an incompatible structure.

  • sanjayadileepa-1150898

    SSC-Addicted

    Points: 420

    I need to take database backups by overwriting the existing backup Programmatically with CompressionOption on. But the following error occurs when running the code.

    The backup cannot be performed because 'COMPRESSION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'COMPRESSION' or specify 'NO_COMPRESSION'. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

    BACKUP DATABASE is terminating abnormally.

    My Code is as follows

    Public Function BackupWithCompression(ByVal conString As String, ByVal databaseName As String, _

    ByVal backupDevicePath As String, ByVal backupDeviceName As String) As Boolean

    Try

    Dim server As Server

    Dim database As New Database

    Dim backup As New Backup

    Dim bkpDBFullWithCompression As New Backup()

    Dim connection As New ServerConnection(New SqlConnection(conString))

    server = New Server(connection)

    database = CType(server.Databases.Item(databaseName), Database)

    backup.Database = database.Name

    backup.Initialize = True

    Dim backupFileName As String = backupDevicePath & backupDeviceName

    backup.Action = BackupActionType.Database

    backup.Database = database.Name

    backup.CompressionOption = BackupCompressionOptions.On

    backup.Devices.AddDevice(backupFileName, DeviceType.File)

    backup.BackupSetName = "Database Backup compressed"

    backup.BackupSetDescription = "database full Backup with Compression"

    backup.SqlBackup(server)

    Catch ex As Exception

    Throw

    End Try

    Return True

    End Function

    Please help me to solve this problem.

    Thanks

  • Tom Van Zele

    SSC Veteran

    Points: 207

    Could it be that you are trying to append a compressed backup to a non-compressed backup set?

    Tom

    Tom Van Zele | Blog | Twitter | LinkedIn

  • Ray Mond

    SSCertifiable

    Points: 7099

    I'm not familiar with the 'Backup' object, but it doesn't look like you're overwriting the existing backup file. As mentioned in the error message, you should use the WITH FORMAT option to overwrite an existing backup file, otherwise the backup set will just be appended to the existing file. Try setting the FormatMedia property to true, or the Initialize property if you want to preserve the media header.

    Thanks.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • sanjayadileepa-1150898

    SSC-Addicted

    Points: 420

    Yes! you are correct Tom. I'm trying to overwrite non compress backup with compress backup, two different formats. That is the reason for the error. Thank you very much for your reply.

  • Max

    Newbie

    Points: 1

    delete or rename old backup dir,

    backup make new one

     

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply