CODEWITHSUNDEEP
sqlsql-serverdatabasebackup
Zachari Whitney
Zachari Whitney

Reputation: 11

SQL Script to compress database backups if version allows it

I currently use a pretty basic backup script to backup my SQL databases to a given directory, zipped with Winrar.

I am looking to use the SQL compression command (currently commented out) prior to the Winrar IF the version of SQL the script is being used on is SQL Standard or higher.

Here is what my current script looks like:

Declare @backupPath nvarchar(1000);
set @backupPath = 'C:\Backups\Auto\';
Declare @fileName nvarchar(100);
Declare @currentDate datetime
Declare @fullPath nvarchar(1000);
Declare @databaseName nvarchar(100);

set @databaseName = 'Database_name';

-- Do not change these values
set @currentDate = GETDATE();
set @fileName = @databaseName + '_' +                  REPLACE(REPLACE(REPLACE((CONVERT(nvarchar(24), GETDATE(), 120)), ':', ''),' ', ''),'-', '') + '.bak'
set @fullPath = @backupPath + @fileName;
print 'adding device ' + @fileName
EXEC sp_addumpdevice 'disk', @fileName, @fullPath;
BACKUP database @databaseName to @fileName --WITH COMPRESSION
print 'dropping device ' + @fileName
EXEC sp_dropdevice @fileName

I would like the script to check for version/edition, then if the Version/Edition is Standard or higher, to run the WITH COMPRESSION command.

Upvotes: 0

Views: 1748

Answers (3)

TravisWhidden
TravisWhidden

Reputation: 2182

This may not be the full, but I think you will get the point:

DECLARE @databaseName nvarchar(100)
DECLARE @fileName nvarchar(100)
DECLARE @serverEdition int;
DECLARE @useCompression bit;

SELECT @serverEdition = Cast(SERVERPROPERTY('EditionID') as int);
-- Reference: http://stackoverflow.com/questions/2070396/how-can-i-tell-what-edition-of-sql-server-runs-on-the-machine
IF @serverEdition IN    (
                            1804890536,     -- Enterprise
                            610778273,      -- Enterprise Eval
                            -1534726760     -- Standard
                        )    
BEGIN
    useCompression = 1; -- Supports compression
END

if @useCompression
BEGIN
    BACKUP database @databaseName to @fileName WITH COMPRESSION;
END
ELSE
BEGIN
    BACKUP database @databaseName to @fileName;
END

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

Without checking anything. If compression is supported compress, if not error is raised and then backup normally.

BEGIN TRY
    BACKUP DATABASE @databaseName TO DISK = @filename WITH COMPRESSION;
END TRY
BEGIN CATCH
    BACKUP DATABASE @databaseName TO DISK = @filename;
END CATCH

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

IF (CASE WHEN   CAST(SERVERPROPERTY('Edition') AS VARCHAR(100)) LIKE '%Enterprise%' THEN 1 ELSE 0 END 
   + CASE WHEN SERVERPROPERTY('EngineEdition') = 3 THEN 1 ELSE 0 END = 2)
    BEGIN
      BACKUP database @databaseName to @fileName WITH COMPRESSION 
    END
ELSE 
   BEGIN
      BACKUP database @databaseName to @fileName
   END

Upvotes: 0

Related Questions