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.
ERROR CODE : 3013

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

share|improve this question

5 Answers 5

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

Function IsBackupCompressed
{
    Param
        (
            [string]$Server,
            [string]$BAKFile
        )

    [Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.SMO')
    [Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.SMOExtended')

    $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)

    $Res.ReadBackupHeader($SMOServer).Rows[0].Compressed


}

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
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
)
AS
BEGIN
    /*
        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
    */
    SET NOCOUNT ON;
    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
    );
    DECLARE @t TABLE (
        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
    BEGIN
        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 
        BEGIN
            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;
        END
        ELSE
        BEGIN
            /*
                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
        END
    END
    ELSE
    BEGIN
        INSERT INTO @Exists
        EXEC Master.dbo.xp_fileexist @BackupFileName;
        SELECT @FileExists = [File Exists] FROM @Exists E;
    END
    SET @Compressed = 0;
    IF @FileExists > 0
    BEGIN
        SET @cmd = 'RESTORE HEADERONLY FROM DISK=''' + @BackupFileName + ''';';
        INSERT INTO @t
        EXEC sp_executesql @cmd;
        SELECT @Compressed = Compressed FROM @t t;
    END
    SELECT @Compressed;
END
GO

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

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:

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

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

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,
    BindingID UNIQUEIDENTIFIER,
    RecoveryForkID UNIQUEIDENTIFIER,
    Collation NVARCHAR(128),
    FamilyGUID UNIQUEIDENTIFIER,
    HasBulkLoggedData BIT,
    IsSnapshot BIT,
    IsReadOnly BIT,
    IsSingleUser BIT,
    HasBackupChecksums BIT,
    IsDamaged BIT,
    BeginsLogChain BIT,
    HasIncompleteMetaData BIT,
    IsForceOffline BIT,
    IsCopyOnly BIT,
    FirstRecoveryForkID UNIQUEIDENTIFIER,
    ForkPointLSN NUMERIC(25,0) NULL,
    RecoveryModel NVARCHAR(60),
    DifferentialBaseLSN NUMERIC(25,0) NULL,
    DifferentialBaseGUID UNIQUEIDENTIFIER,
    BackupTypeDescription NVARCHAR(60),
    BackupSetGUID UNIQUEIDENTIFIER NULL,
    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
1  
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

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, 
       case 
            when  backup_size = compressed_backup_size then 'Compressed'
            else 'Not compressed'
        end as Compression, 
        backup_finish_date
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

Your Answer

 
discard

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.