Competition: What’s your favorite Redgate tool? Enter now.

SQLBackup ROI Calcluation

SaranSaran Posts: 6
edited November 6, 2007 7:44AM in SQL Backup Previous Versions
How hard would it be to provide the tool with an ROI numbers. I need to show the management that $ that we are saving because of using SQL backup.
It would also enable me to budget for my future
Thanks!

Comments

  • peteypetey Posts: 2,358 New member
    If you are using version 5, here's something you can use to quickly compare the total compressed size against the total native backup size, for all backups made to date using v5:
    EXEC master..sqbdata 'SELECT (SUM(size) / 1048576) TotalNativeMB, (SUM(compressed_size) / 1048576) TotalCompressedMB FROM backuphistory'
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • petey wrote:
    If you are using version 5, here's something you can use to quickly compare the total compressed size against the total native backup size, for all backups made to date using v5:
    EXEC master..sqbdata 'SELECT (SUM(size) / 1048576) TotalNativeMB, (SUM(compressed_size) / 1048576) TotalCompressedMB FROM backuphistory'
    

    I ran this command and I think this takes information from the msdb database. As we clean the history regularly from the msdb database, the numbers that we get, may not reflect the complete picture.

    Any suggestions ?
  • peteypetey Posts: 2,358 New member
    The data is actually being retrieved from SQL Backups' internal database, not from the msdb tables. Yes, you are right in that it will not contain all the necessary information if historical data is periodically removed. The workaround would be to periodically (maybe once a month) get the total disk space saved for a given period.

    While this addreses the space savings, there is also the element of time saved in using compressed backups. If the restore time is improved in your setup (and it is most of the time), I am sure management would agree that being able to get up and running in half the time (or faster) in the event of a disaster recovery scenario is priceless.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Would it be OK, if I could ask you of how to access SQL backup internal database, apart from the command you had posted.

    That would help me in creating a datastore, from which I could derive the information that I require. I am comfortable running this command directly from my reporting tool

    Thanks again!
  • peteypetey Posts: 2,358 New member
    SQL Backups' internal data is stored in a SQL Server Compact Edition (SSCE) database. The data file is located in the <system folder>:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data\<instance name> folder. The file name is data.sdf.

    You can look up the SSCE documentation to see how to connect to the data store via OLEDB, .Net etc. In addition, the sqbdata extended stored procedure acts as a proxy to the data store. When you issue a DML statement via sqbdata, it is routed to the SQL Backup Agent service. The Agent then accesses the SSCE database, and returns the results to you as returned by SSCE. In this way, you do not need direct access to the data file.

    There are only a handful of tables in the data store, and the structure is similar to those found in the msdb tables. Using Management Studio will allow you to browse the data model and content easily.

    Feel free to ask if you have any other questions.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi Saran,

    We are currently working with an independent third party on making ROI information for SQL Backup available to our customers. We estimate that this work will be completed in September this year, so please do keep an eye out for further details nearer the time. Alternatively, if you would like us to e-mail you directly once the work is finished, please feel free to contact my colleague, Claire Brooking, at [email protected] regarding your question, and she will happily be in touch.

    Regards,
    Helen
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
  • As a follow-up to my earlier post, I’m pleased to confirm that we now offer a case study and tool for examining potential ROI with SQL Backup from the SQL Backup product pages: http://www.red-gate.com/products/SQL_Backup/ROI_case_study.htm.
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
Sign In or Register to comment.