Red Gate SQL Backup 5.4 vs SQL Server 2008 Backup

RedGate 5.4 Backup Vs SQL 2008 Backup Vs SQL 2008 Backup with Encryption

RedGate 5.4 Encrypted Compressed Backup

How it works?

1. Backup the database,

2. Compress the Database,

3. Encrypt the Database with a Password,

4. Password needs to be supplied while restoring/backing up the database, or password needs to be place in script in job.

Database size for testing

7 GB

Backup Time

                134 Secs

Backup Size

                715 MB

Restore Time

                247 Seconds.

Encryption

                Yes, 256 Bit.

Compression Level

                3 - Full (Available options, 1, 2, 3)

Advantages

1. Encrypts the Backup file, not the database.

2. Flexible Compression levels can be used.

3. Compression Ratio is large than SQL Compression.

4. Backup can be split into multiple files.

Limitations

                1. Requires to buy another License.

2. Password is displayed on backup/restore script, anyone can see the password, who has access to view the jobs.

Tested Script

DECLARE @exitcode int

DECLARE @sqlerrorcode int

exec master..sqlbackup N'-SQL "BACKUP DATABASES [myDB]  TO DISK = ''C:\SQL Data\backup\'' WITH NAME = '''', DESCRIPTION = '''', ERASEFILES = 5, COMPRESSION = 1, THREADS = 1,MAILTO = ''DBATeam@Domain.com''"', @exitcode OUTPUT, @sqlerrorcode OUTPUT

IF (@exitcode <>0) OR (@sqlerrorcode <> 0)

BEGIN

  RAISERROR ('SQL Backup job failed with exitcode: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode

END

SQL 2008 Ent Full Backup Compressed

How it works?

1. Backup the Database,

2. Compress the Database,

3. Password Protection is available, however without encryption. Open URL below for more information - http://www.mssqltips.com/tip.asp?tip=1108

Database size for testing

7 GB

Backup Time

120 Seconds

Backup Size

865 MB

Restore Time

246 Seconds

Encryption

No.

Compression Level

Full, Only Option

Advantages

1. No Need to purchase extra License, this is part of SQL 2008 License.

2. Password protection is available to backups.

3. Supported by Microsoft as part of Microsoft Product.

4. Compresses the Database.

Limitations

1. Does not support Encryption.

Tested Script

BACKUP DATABASE MyDB TO DISK= 'C:\SQL Data\backup\myDB.bak'

WITH COMPRESSION

GO

RESTORE DATABASE  MyDB FROM DISK = 'C:\SQL Data\backup\myDB.bak'

WITH RECOVERY,

MOVe 'DATA1' TO 'C:\SQL Data\Test\DATA1.mdf',

Move 'LOG1' TO 'C:\SQL Data\Test\LOG1.ldf',

Move 'INDEX1' To 'C:\SQL Data\Test\INDEX1.mdf'

GO

SQL 2008 Ent Full Backup Encrypted Compressed

How it works?

1. SQL 2008 supports Encryption at database level and encryption has to be enabled first time using the following steps.

     a. A database certificate has

          to be created followed by a Master Key.

     b. Using certificate, Database Encryption Key

          can be created to make database encrypted.

     c. Encryption can be enabled on database at

         this point (CPU Intensive task).

2. Backup the Encrypted Database.

3. Compresses the database.

Database size for testing

7 GB

Backup Time

218 Seconds

Backup Size

                4.73 GB

Restore Time

                337 Seconds

Encryption

                Yes, 256 bit.

Compression Level

                Full, Only Option

Advantages

1. No need to display the password on restore command or backup command. SQL Server uses the inbuilt database certificate for security.

2. No Need to purchase extra License, this is part of SQL 2008 License.

Limitations

1. CPU Intensive Task.

2. Backup Compression ratio least of other two option.

3. In case certificate is lost, we can lose the Data.

Tested Script

--1. CREATE MASTER ENCRYPTION KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*****'

--2. CREATE DATABASE CERTIFICATE

CREATE CERTIFICATE dbCERT WITH SUBJECT = 'Database Certificate TDE'

--3. BACKUP CERTIFICATE

BACKUP CERTIFICATE dbCERT TO FILE = 'C:\SQL Data\DB CERTIFICATE\db_bkupcert_1.cert'

WITH PRIVATE KEY(

 FILE = 'C:\SQL Data\DB CERTIFICATE\db_bkupcert_1.cert', ENCRYPTION BY PASSWORD = '*****');

--4. CREATE DEK -- DATABASE ENCRYPTION KEY

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE dbCERT

--5. ENABLE ENCRYPTION ON SELECTED DATABASE

ALTER DATABASE myDB SET ENCRYPTION ON

--6. ABOVE COMMAND WILL START A BACKUP PROCESS OF ENCRYPTING DATABASE, MONITOR THE STATUS OF ENCRYPTION PROCESS USING

--FOLLOWING COMMAND.

SELECT db_name(database_id), encryption_state,percent_complete

FROM sys.dm_database_encryption_keys