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.
