SQLBackup and Compressed SQL2008 Databases

ChrisAVWoodChrisAVWood Posts: 361
edited October 1, 2008 1:30PM in SQL Backup Previous Versions
Hi,

I understand that SQLBackup 5.3 works with SQL2008 and 5.4 is needed if you are running on Windows 2008 Server. Hopefully 5.4 will be released soon.

SQL2008 includes the ability to compress the actual contents of the database at both the row and page levels. Page level compression appears to be a good idea. How does SQLBackup work with compressed SQL2008 databases?

Thanks

Chris
English DBA living in CANADA

Comments

  • Thank you for your post into the forum.

    SQL Backup will successfully backup and restore databases that have Page Level Compression configured.

    I encountered no errors or any significant effects on performance.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Eddie,

    You didn't mention if this was both SQLBackup 5.3 on W2K3 and/or SQLBackup 5.4 on W2K3 and/or W2K8?

    Thanks

    Chris
    English DBA living in CANADA
  • peteypetey Posts: 2,358 New member
    Since version 1, SQL Backup uses the SQL Server Virtual Device Interface (VDI) to perform backups and restores. This is the official interface for ISVs' to use who want to implement extended backup and restore functionality for SQL Server. Using this interface, SQL Backup simply works with the backup data that is passed by SQL Server to it during a backup, and simply passes the backup data back to SQL Server during a restore. See this document for more details.

    So if your database contains compressed data, SQL Backup is unaware of it. SQL Server will simply pass the backup data to SQL Backup, and SQL Backup will compress/encrypt it and store it to disk. Obviously, the data cannot be compressed much, if at all, as the data is already compressed by SQL Server. If most of your database contains compressed data, you may not want to use SQL Backup to perform your backups. Or if you want to encrypt your backups, you could use compression level 0, which skips the SQL Backup compression process.

    You require SQL Backup 5.4 in certain cases if you are using Windows Server 2008 (see this post). If 5.3 works on your Windows Server 2008 box, then fine. As for the backup and compression process, there is no SQL Server 2008-specific functionality in either 5.3 or 5.4.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks Pete. I understand how SQLBackup works. I was just asking what version of SQLBackup Eddie had used his in his tests.

    Chris
    English DBA living in CANADA
  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi Chris

    I used SQL Backup 5.4.0.23 runnning on a Windows 2008 Server with SQL 2008 RTM.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Just what I wanted to know Eddie. Is there a date for 5.4 being generally available?

    Thanks

    Chris
    English DBA living in CANADA
Sign In or Register to comment.