Select Page

Retrofit Vintage SQL 2005 Databases

Author: Andy McDermid | 3 min read | May 31, 2016

Do you have a few old 2005 or 2008 SQL Server instances chugging along that you know will never ever be upgraded to SQL 2012, 2014 or 2016? For whatever reason – technical, political, or practical – there is no plan to version-up these servers and the databases will live the rest of their life as version 9 or 10, or 10.5? Let’s call them vintage DBs. It’s not a bad thing; vintage is hip these days and if the database still gets you where you’re going, well then, no big deal. But then again, the inherent limited functionality and special exceptions around administering vintage databases within an otherwise up-to-date environment can increase the complexity of management.

For instance, here are a couple of very handy backup features included in more recent versions of SQL that you don’t get with vintage DBs:

  • Native SQL Backup Compression – built-in for SQL2008 and beyond, for the price of higher CPU usage SQL Server database backups can be compressed via the TSQL backup statement. Compression ratios depend on the type of data, but the decreased duration of backup operations and the storage space savings make WITH COMPRESSION a no-brainer in many cases.
  • Native SQL Backup Encryption – Transparent Data Encryption (TDE) was introduced for SQL 2008 to encrypt data at rest and that includes the data files as well as the database backups. A few drawbacks though … TDE is Enterprise Edition only and when TDE is in use backups get a very poor compression ratio due to the encrypted data. Backup Encryption as a stand-alone feature was built into SQL 2014 and allows the DBA to encrypt (and compress effectively) the backup in Enterprise or Standard Edition.

There is plenty of great new functionality in the latest versions but I wanted to point these two backup-related features because there is in-fact a way to apply them to your vintage databases.

Take a look at the Microsoft SQL Server Backup to Windows Azure Tool. In summary, this tool runs as a service and will “watch” a local directory you specify. When a database backup file from any standard SQL database backup lands in the directory the service routes it to an Azure Storage account.

Backup to URL and Managed Backups are only available in recent versions of SQL Server so the primary idea behind this tool is to give pre SQL2012 SP1 CU2 instances the capability to backup to Azure Storage. That is pretty cool in and of itself. Additionally, this tool gives 2005 and 2008 instances the same backup compression and backup encryption capabilities as later versions of SQL Server. Not only can you send your SQL 2005 database backups to the cloud, you can encrypt them and compress them along the way…


And, what’s more, although sending those encrypted and\or compressed backup to Azure Storage is certainly an intriguing option, it’s not required. The tool also allows backups to local disk. So even if you’re not ready for cloud storage, the compression and encryption functionality might be welcome to save on-premise storage space, increase security, and maintain database backup plan standards across multiple SQL instances, including the vintage models.

Read through the document in the download link for all the details and caveats and let me know what you think. Meanwhile I’ll be retrofitting my ol’ 2005 clunker of a backup plan with some sweet 2014 database backup compression.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

PostgreSQL vs. Oracle: Let’s Compare

Discover some key differences between PostgreSQL vs Oracle that can help you make an informed decision when deciding on a database management system.

Vijay Muthu | March 30, 2021

Oracle RMAN Backup and Recovery with Restore Points

Oracle restore points are useful for benchmark testing. Find out how you can use Oracle’s Recovery Manager (RMAN) tool to create and use restore points.

Cindy Putnam | May 3, 2019

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.