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
I have heard about another sql mdf repair tool. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.
ReplyDelete