Want to know how much it’ll cost to back up your database transaction logs to Amazon S3?  I’ll show you with a snippet of T-SQL.

Note the parameters at the top for retention periods.  The defaults assume one full backup weekly.  DBAs would normally want to do full backups more often, but when you’re paying by the upload, you may want to consider only uploading one full backup per week and then uploading transaction log backups the rest of the time.  You can still take full and differential backups locally for faster local recovery times as long as you set your backup parameters correctly so that you don’t break your log chain.

The @months_to_retain_full_backups parameter says we’re storing two months of full backups in S3.  This gives you some offsite flexibility while minimizing your upload costs.

The upload cost is set at $.10 per gig because that’s Amazon’s long-term price.  There’s a temporary sale going on for $.03 per gig for uploads, but you can’t set your budget by that.

With no further ado, here’s the query.  If it doesn’t work on your system, let me know – this is a really rough draft that I’ve only used on a dozen or so systems, and it wouldn’t surprise me if it’s got bugs.

DECLARE  @upload_cost_per_gb              DECIMAL(6,2),
         @storage_cost_per_gb             DECIMAL(6,2),
         @days_to_retain_log_backups      INT,
         @full_backups_uploaded_per_month INT,
         @months_to_retain_full_backups   INT
 
SET @upload_cost_per_gb = .10
SET @storage_cost_per_gb = .15
SET @days_to_retain_log_backups = 14
SET @full_backups_uploaded_per_month = 4
SET @months_to_retain_full_backups = 2
 
SELECT   bs.database_name,
         COUNT(* ) AS backups,
         SUM(bs.backup_size / 1000000000) AS transaction_backup_size_gb,
         SUM(bs.backup_size / 1000000000 * @upload_cost_per_gb) AS transaction_backup_upload_cost,
         SUM(bs.backup_size / 1000000000 * @storage_cost_per_gb / 30 * @days_to_retain_log_backups)
                   AS transaction_backup_storage_cost,
         (SELECT   TOP 1 (fbs.backup_size / 1000000000)
          FROM     msdb.dbo.backupset fbs
          WHERE    bs.database_name = fbs.database_name
                   AND fbs.type = 'D'
          ORDER BY backup_start_date DESC) AS last_full_backup_size_gb,
         (SELECT   TOP 1 (fbs.backup_size / 1000000000 * @upload_cost_per_gb
                          * @full_backups_uploaded_per_month)
          FROM     msdb.dbo.backupset fbs
          WHERE    bs.database_name = fbs.database_name
                   AND fbs.type = 'D'
          ORDER BY backup_start_date DESC) AS full_backup_upload_cost,
         (SELECT   TOP 1 (fbs.backup_size / 1000000000 * @storage_cost_per_gb
                          * @full_backups_uploaded_per_month * @months_to_retain_full_backups)
          FROM     msdb.dbo.backupset fbs
          WHERE    bs.database_name = fbs.database_name
                   AND fbs.type = 'D'
          ORDER BY backup_start_date DESC) AS full_backup_storage_cost
FROM     msdb.dbo.backupset bs
WHERE    bs.type = 'L' /* log backups only, not diffs or fulls */
         AND bs.backup_start_date >= DATEADD(dd,-30,GETDATE())
GROUP BY bs.database_name
ORDER BY bs.database_name

This query revolves around transaction log backups, but if you only do incrementals, you could change that bs.type = “L” filter in the query to be “I” instead, and you’ll get the cost on incrementals.  I don’t have a query for databases that only do full backups, but frankly, if you’re only doing fulls, your data probably isn’t important enough to get internet-based online backups available.

This query is good for more than just gauging costs, too: keep in mind that your internet connection will need to be able to handle uploading your backups out to Amazon S3.

This is only a ballpark estimate. There are other costs that this query does not cover.  For example, Amazon charges you for some types of requests, like checking directory contents.  These costs can vary, but they’re generally very small relative to the bandwidth and storage costs.  This query also doesn’t check for backup compression, but if you’re paying by the byte for bandwidth and storage, you should seriously consider using backup compression.  The ROI is extremely high: software like Quest LiteSpeed pays for itself here in no time.

If this topic interests you, I’ll be discussing it in much more detail during my “Log Shipping To The Cloud” session at the SSWUG Virtual Conference on Thursday.  (Use VIP code SPVBOZSP09 for $10 off the $125 entry price.)  If you register for the V-Conference, you can watch the sessions whenever it’s convenient for you – not just Thursday.  However, if you happen to watch it during the first showing on Thursday, I’ll be online in the chat room for the session and you can ask me questions live.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts