Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have recently upgraded from SQL Server 2005 to SQL Server 2012. Under SQL Server 2005 there is no option to create compressed backups as there is in 2012.

If you attempt BACKUP DATABASE ... WITH (COMPRESSION); to a file that has already been initialized without compression, the BACKUP DATABASE command will fail with the following error message:

ERROR MESSAGE : BACKUP DATABASE is terminating abnormally.

How can I tell if an existing backup file is initialized for compressed backups?

share|improve this question
add comment

5 Answers

up vote 5 down vote accepted

I created the following stored procedure that can be used to determine if a database backup file is initialized for compression:

CREATE PROCEDURE IsBackupFileCompressed
    @BackupFileName nvarchar(255)
    , @UseXPFileExists bit = 1
        Inspects the header of the given backup file to see if the
        file contains a SQL Server compressed backup.

        Returns 1 if the backup is compressed, 0 if uncompressed.

        By:     Max Vernon
        Date:   2013-03-26
    DECLARE @FileExists bit;
    DECLARE @Compressed bit;
    DECLARE @cmd nvarchar(max);
    DECLARE @ShellText NVARCHAR(512);
    DECLARE @ShellResults TABLE (
        ShellText nvarchar(255)
    DECLARE @Exists TABLE 
        [File Exists] bit
        , [File is a Directory] bit
        , [Parent Directory Exists] bit
        BackupName nvarchar(255)
        , BackupDescription nvarchar(255)
        , BackupType    int
        , ExpirationDate    datetime
        , Compressed    int
        , Position  int
        , DeviceType    int
        , UserName  nvarchar(255)
        , ServerName    nvarchar(255)
        , DatabaseName  nvarchar(255)
        , DatabaseVersion   int
        , DatabaseCreationDate  datetime
        , BackupSize    numeric(38,0)
        , FirstLSN  numeric(38,0)
        , LastLSN   numeric(38,0)
        , CheckpointLSN numeric(38,0)
        , DatabaseBackupLSN numeric(38,0)
        , BackupStartDate   datetime
        , BackupFinishDate  datetime
        , SortOrder int
        , CodePage  int
        , UnicodeLocaleId   int
        , UnicodeComparisonStyle    int
        , CompatibilityLevel    int
        , SoftwareVendorId  int
        , SoftwareVersionMajor  int
        , SoftwareVersionMinor  int
        , SoftwareVersionBuild  int
        , MachineName   nvarchar(255)
        , Flags int
        , BindingID uniqueidentifier    
        , RecoveryForkID    uniqueidentifier
        , Collation nvarchar(255)
        , FamilyGUID    uniqueidentifier
        , HasBulkLoggedData int
        , IsSnapshot    int
        , IsReadOnly    int
        , IsSingleUser  int
        , HasBackupChecksums    int
        , IsDamaged int
        , BeginsLogChain    int
        , HasIncompleteMetaData int
        , IsForceOffline    int
        , IsCopyOnly    int
        , FirstRecoveryForkID   uniqueidentifier
        , ForkPointLSN  numeric(38,0)
        , RecoveryModel nvarchar(255)
        , DifferentialBaseLSN   numeric(38,0)
        , DifferentialBaseGUID  uniqueidentifier
        , BackupTypeDescription nvarchar(255)
        , BackupSetGUID uniqueidentifier
        , CompressedBackupSize  numeric(38,0)
        , Containment int

    SET @FileExists = 0;
    IF @UseXPFileExists = 1
        DECLARE @IsXPCmdShellEnabled BIT;
        SET @IsXPCmdShellEnabled = CAST(
                SELECT top(1) value_in_use 
                FROM sys.configurations c 
                WHERE c.name = 'xp_cmdshell'
            ) as bit);
        IF @IsXPCmdShellEnabled = 1 
            SET @ShellText = 'dir /b ' + @BackupFileName
            INSERT INTO @ShellResults
            exec xp_cmdshell @ShellText;
            SELECT @FileExists = COUNT(*) 
                FROM @ShellResults S 
                WHERE @BackupFileName LIKE ('%' + s.ShellText) 
                    AND s.ShellText IS NOT NULL;
                This is a fallback in case XP_CMDSHELL is disabled
                Unfortunately, this will trigger a SEV16 error if the file does not exist, 
                setting off alarm bells all over the place
            BEGIN TRY
                SET @cmd = 'RESTORE LABELONLY FROM DISK=''' + @BackupFileName + ''';';
                EXEC sp_executesql @cmd;
                SET @FileExists = 1;
            END TRY
            BEGIN CATCH
                SET @FileExists = 0;
            END CATCH
        INSERT INTO @Exists
        EXEC Master.dbo.xp_fileexist @BackupFileName;
        SELECT @FileExists = [File Exists] FROM @Exists E;
    SET @Compressed = 0;
    IF @FileExists > 0
        SET @cmd = 'RESTORE HEADERONLY FROM DISK=''' + @BackupFileName + ''';';
        INSERT INTO @t
        EXEC sp_executesql @cmd;
        SELECT @Compressed = Compressed FROM @t t;
    SELECT @Compressed;

This stored proc demonstrates how to determine if a file exists in various ways, including using xp_cmdshell with a TRY...CATCH block in case xp_cmdshell is not enabled, and my preferred method, xp_fileexists.

I'm not incredibly happy with how this stored proc works - I would prefer a more light-weight version.

share|improve this answer
I'm accepting this answer since it fulfills my needs. Thanks everyone for your input! –  Max Vernon Apr 1 '13 at 21:23
add comment

Maybe instead of backing up over and over again to the same file, you should consider using WITH INIT and a new file always. I think it is simpler to manage multiple backup files, all with their own timestamp embedded into the filename, and be able to archive/purge each file individually. When you keep dumping backups to the same file, it just gets bigger and bigger, and harder to manage IMHO. Never mind that you no longer have to care that

Also I am not sure why you would ever be turning backup compression on and off. Have you found a case where disabling it is better? Do you have real use cases where on an edition that supports compression you are taking one-off backups without compression, and using the same file? Why?

Anyway you can always do something very simple like:

    BACKUP DATABASE x TO DISK = 'c:\wherever\x.bak' WITH COMPRESSION, ...;
    BACKUP DATABASE x TO DISK = 'c:\wherever\x.bak', ...;

The error bubbles up immediately before any work is done.

But I still think it is much better to just not use the same file over and over again in the first place. IMHO.

share|improve this answer
Good questions. The stored proc that actually does the backup uses the same file name each time it does a backup. However, the command uses WITH NOFORMAT, INIT unless I manually specify that I want to FORMAT the file. I believe NOFORMAT, INIT effectively re-uses the file without having to issue an OS command to create an empty file first. When I do FORMAT, I see a large amount of file system activity before the backup takes place. –  Max Vernon Mar 26 '13 at 17:55
So far we have only ever turned compression ON. However, since we are using the same target file names each time, switching from no compression to compression necessitates the FORMAT option the first time around. –  Max Vernon Mar 26 '13 at 17:56
@Max have you measured the impact FORMAT actually has? Have you measured the duration your heavy-handed method incurs? Which one is worse? –  Aaron Bertrand Mar 26 '13 at 17:57
@Max also if your goal is to simply wipe out the existing backup, why not just issue xp_delete_file, and ignore it if it doesn't find the file? You're doing a lot of work to check an existing file that you care absolutely nothing about. –  Aaron Bertrand Mar 26 '13 at 17:59
I may be working around an issue with our SAN. Without rigorously testing this (in a lab environment), I have seen disastrous impact on our SAN performance from simply deleting a large file (by large, I'm talking half a terabyte). The INIT option appears to do less I/O work than the FORMAT option, so we use INIT where possible. –  Max Vernon Mar 26 '13 at 18:34
add comment

Here's a handy powershell function to do the same thing:

Function IsBackupCompressed


    $SMOServer = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
    $Res = New-Object Microsoft.SqlServer.Management.Smo.Restore

    $Res.Devices.AddDevice($BAKFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)



Pass it your server name and path to the .BAK file and it will return a 1 or 0 for compression.

Bear in mind the .BAK file path needs to be relative to wherever you run the script from!

If this is going into production you should add some error trapping for things like the file not existing.

share|improve this answer
add comment

To see if a particular database backup file was backed up using compression or not you can interrorgate the header information of the file:

RESTORE HEADERONLY FROM DISK = 'Path to backup file';

One of the columns returned is labelled "Compressed". 1 = compressed, 0 = not compressed.

You could load that info into a temp table if you need to have some control flow about whether you can reuse the file or need to reinitialise it. Something like:

DECLARE @RestoreHeader TABLE (
    BackupName NVARCHAR(128),
    BackupDescription NVARCHAR(255),
    BackupType SMALLINT,
    ExpirationDate DATETIME,
    Compressed BIT,
    Position SMALLINT,
    DeviceType TINYINT,
    UserName NVARCHAR(128),
    ServerName NVARCHAR(128),
    DatabaseName NVARCHAR(128),
    DatabaseVersion INT,
    DatabaseCreationDate DATETIME,
    BackupSize NUMERIC(20,0),
    FirstLSN NUMERIC(25,0),
    LastLSN NUMERIC(25,0),
    CheckpointLSN NUMERIC(25,0),
    DatabaseBackupLSN NUMERIC(25,0),
    BackupStartDate DATETIME,
    BackupFinishDate DATETIME,
    SortOrder SMALLINT,
    CodePage SMALLINT,
    UnicodeLocaleId INT,
    UnicodeComparisonStyle INT,
    CompatibilityLevel TINYINT,
    SoftwareVendorId INT,
    SoftwareVersionMajor INT,
    SoftwareVersionMinor INT,
    SoftwareVersionBuild INT,
    MachineName NVARCHAR(128),
    Flags  INT,
    Collation NVARCHAR(128),
    HasBulkLoggedData BIT,
    IsSnapshot BIT,
    IsReadOnly BIT,
    IsSingleUser BIT,
    HasBackupChecksums BIT,
    IsDamaged BIT,
    BeginsLogChain BIT,
    HasIncompleteMetaData BIT,
    IsForceOffline BIT,
    IsCopyOnly BIT,
    ForkPointLSN NUMERIC(25,0) NULL,
    RecoveryModel NVARCHAR(60),
    DifferentialBaseLSN NUMERIC(25,0) NULL,
    BackupTypeDescription NVARCHAR(60),
    CompressedBackupSize BIGINT,
    containment TINYINT NOT NULL

INSERT INTO @RestoreHeader
EXEC ('RESTORE HEADERONLY FROM DISK = ''Path to backup file''');

SELECT Compressed FROM @RestoreHeader
share|improve this answer
Very good, but Max's own solution already does that :-). His problem is that he doesn't like the solution as it seems a bit clunky. –  Marian Mar 27 '13 at 10:47
add comment

I'm not sure the following will help you (as there have been many great answers), but if you have access to the backup history, I guess you could determine whether previous backups were compressed or not with a query similar to this one:

select top(10) database_name, 
            when  backup_size = compressed_backup_size then 'Compressed'
            else 'Not compressed'
        end as Compression, 
from msdb.dbo.backupset
where database_name ='myDB'
order by backup_start_date desc
share|improve this answer
I could see how that would be helpful. Thanks for the answer. In my particular instance I'm trying to check the physical file, which might have come from a prior instance or a remote machine. –  Max Vernon Mar 27 '13 at 21:49
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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