We get a fair number of questions from SQL Backup Master users about transaction log files, often accompanied by concerns regarding truncation and/or shrinking of log files.
We’ll start with the summary. It isn’t necessary to manually truncate SQL Server transaction log files. However, there may be an occasional need to manually shrink them.
We’ll talk about log truncation first. Truncation is a process that frees up space in the log file for reuse. It will occur automatically at regular checkpoints if your SQL Server database is using the simple recovery model. If the database uses the full or bulk-logged recovery model, truncation occurs automatically after each log backup.
Which recovery model your database should use depends on whether your recovery plan includes a requirement for point-in-time recovery. If not, then use the simple recovery model. Otherwise, SQL Server needs to use the full or bulk-logged recovery model and transaction log backups become a requirement (otherwise they will not truncate and will continually grow the log file).
Note: The default recovery model for SQL Server depends on the product edition. SQL Server Express defaults the recovery model to SIMPLE, while most other editions default to FULL.
Which brings me to the final topic of this blog – log file shrinkage. This should be an uncommon operation that is performed manually (usually as part of a database migration, bulk import, etc.), and not part of any maintenance or scheduled backup operation.
It’s a simple question. Does your company operate a SQL Server database for anything important?
If you’re unsure, keep in mind that various editions of Microsoft SQL Server are quite ubiquitous as the data storage back-end for a wide range of modern software products. SQL Server instances also often support internal software and web development operations, with their footprint expanding considerably due to recent (and very robust) support for Linux.
But yes – you’re running SQL Server in some capacity. Why else would you be here given the title of this blog? And you must have a backup strategy for the databases you care about (which is often all of them).
But you already know that you need database backups. And maybe you’re using our SQL Backup Master tool to help with that. That would be good.
But there’s something even more critical here than SQL Server backups themselves. It’s the data they contain, and your ability to restore said data. And yet, this part of the overall backup strategy gets missed a lot, perhaps to the peril of an admin who’s just realizing key backups weren’t running (permissions error) and error notifications were going to an unmonitored email account. Yikes.
Such sullen scenarios, however, are easily avoidable. We’ll use a simple inspirational phrase to help us remember.
Backups are useless unless they can be restored – and you must regularly ensure that they can be.
So we return to the title of this blog entry – the importance of database backup recovery testing. At a minimum, define a test plan that reflects the potential impact of data loss to your organization. Take the time to catalog the SQL Server instances on your network, assessing the criticality of each.
And keep in mind that SQL Backup Master is a perfect solution for any instances of SQL Server that need backing up. It makes it easy (and free) to store backups of any SQL Server instance’s databases in the cloud (or on a NAS, FTP site, etc.).
Finally, use your planning results to inform and execute end-to-end recovery tests at regular intervals. Skip this step only if you value good fortune over verifiable results.
Good news! Our friends over at Backblaze have implemented an S3 Compatible API for their B2 Cloud Storage service. This means you can begin using SQL Backup Master to back up SQL Server databases to Backblaze B2 Storage Cloud immediately.
Backblaze has developed the following guidelines for using their S3 compatible API:
Important: As noted in the article above, B2 Storage buckets created prior to May 4th, 2020 are not S3 compatible. You can work around this by simply creating a new bucket for storing your database backups.
Overall, we think this is excellent news for SQL Backup Master users. Thanks to the recent development work put forth by Backblaze, you can now safely store SQL Server backups in their B2 Cloud Storage platform – one of the most robust and cost effective storage options available today.
For details on how to integrate SQL Backup Master with S3 compatible services, please see our blog post on the subject.
Key Metric Software is proud to announce the release of SQL Backup Master v4.6, with official support for SQL Server 2019. Other improvements include NTFS long path support for folder backup destinations, improved logging, and updated communications libraries for a number of popular backup destination types.
Download the latest update from our product website:
In SQL Backup Master v4.5, we added support for the rapidly growing number of storage services and devices that offer Amazon S3 protocol compatibility.
A good example of such a service is Wasabi, a secure cloud storage provider that bills itself as being “simple, affordable, and fast”. You can learn more about their commitment to S3 protocol compatibility here.
Configuring SQL Backup Master to use Wasabi (rather than Amazon S3) is extremely simple. Just configure the Amazon S3 backup destination to override the Service URL, as shown in the screenshot below.
It’s important to note that some S3-compatible storage services (including Wasabi) will not support every Amazon S3 feature. For example, Wasabi does not (at the time of this writing) support bucket-level transfer acceleration or the GovCloud regional endpoint – and will silently ignore any attempts to use such features. For this reason, we recommend that you always make sure you review any S3-compatible storage provider’s compatibility statements prior to using them.
Still, Wasabi worked very well in our SQL Backup Master compatibility tests – and there are many other examples of Amazon S3 compatible services and hardware devices (including QNAP QTS) available.
Key Metric Software is proud to announce the release of SQL Backup Master v4.5.
This new version includes a number of helpful improvements, including formal support for Windows Server 2019, custom script execution timeout support, the ability to export backup logs as HTML, and much more.
For a full list of changes, please see the SQL Backup Master product release notes.
Did you know that SQL Backup Master makes it easy to back up SQL Server databases running on Linux?
Using SQL Backup Master for this task offers a broad range of benefits:
- Provides backup scheduling for SQL Server (including Express edition) on Linux
- Allows database backups to be stored on-site (local folders, network folders, NAS devices, etc.)
- Allows database backups to be stored off-site (FTP, Dropbox, Google Drive, Amazon S3, OneDrive, etc.)
- Provides database backup compression and encryption capabilities
- Provides email notification support for database backup jobs
- Many other features (robust backup logging, retry support, recovery support, etc.)
For details, please see our Backing Up SQL Server on Linux article.
SQL Backup Master v4.3 introduced the ability to pass custom parameters to the database backup command that is sent to SQL Server. One example of how this capability can be useful is if you wish to enable SQL Server’s built-in backup compression capability.
Before you begin, though, keep in mind that native backup compression is available only in certain versions and editions of SQL Server. You’ll need the Enterprise or Standard edition of SQL Server 2008 R2 or later to use this feature – it isn’t supported in lesser editions such as Express, Web, etc.
It’s also worth noting that SQL Backup Master’s compression feature will typically yield superior results than SQL Server’s built-in backup compression. However, SQL Server’s backup compression feature has the advantage of requiring less working disk space and in some cases might be a better fit for your backup workflow.
To enable SQL Server backup compression in SQL Backup Master:
- Double-click your backup job in the SQL Backup Master main window
- Click the Backup job settings link
- Select the Database tab
- Enable the Append the following arguments to the backup database command checkbox
- Add the keyword COMPRESSION to the backup database command arguments field
The result is shown in the screen shot below.
Next, you’ll likely want to disable SQL Backup Master’s compression feature. Although you aren’t strictly prohibited from using it in conjunction with SQL Server’s built-in backup compression, you won’t typically see much (if any) benefit from doing so. To disable it, select the Compression & Encryption tab in the backup job settings window and select the Compression Disabled option.
You can now save your changes and execute the backup job. The resulting database backup will have a .bak file extension and should be appreciably smaller than its uncompressed counterpart.
IMPORTANT: We strongly recommend that you run backup and restore tests every time you change your backup job settings – especially when using advanced features like the one described above.
In rare cases, SQL Backup Master may log the following error when attempting to perform a differential database backup:
Failed to create backup: Cannot perform a differential backup for database “XYZ”, because a current database backup does not exist.
This error occurs because the SQL Server backup chain for the specified database is being invalidated. Let’s review possible causes.
Possible Cause #1 – VSS (Volume Shadow Copy Service) Backups
Some backup solutions (including NTBackup) will take a snapshot of your SQL Server database via the Windows Volume Shadow Copy Service. These snapshots will invalidate any previous differential backups, thereby interfering with tools such as SQL Backup Master.
VSS creates database backups through a Windows system service called SQL Writer Service. When enabled, this service allows backup applications to copy SQL Server data files while SQL Server is running.
The solution in this case is simple – disable the SQL Writer Service, as follows:
- Press Windows + R keys to open the Run dialog, then type services.msc and press Enter.
- Find the SQL Server VSS Writer service and double-click on it.
- Click the Stop button to stop the service, when change the startup type to Manual.
- Click OK to finish.
When the service has been disabled, use SQL Backup Master to trigger a full database backup to re-establish the state of the backup chain.
Possible Cause #2 – Changing the Recovery Model
If the SQL Server database recovery model has recently changed, you should manually trigger a new full backup in order to reinstate the database backup chain. Once this is done, future differential backups should run successfully.
Possible Cause #3 – SQL Server 2005 Bug
Microsoft knowledge base article #921106 describes a bug in SQL Server 2005 that can cause this problem. The KB article also provides a hot fix.
Still Need Help?
If you’ve reviewed the above causes and solutions are still experiencing this problem, please feel free to contact our support team. We’re always happy to help.
When searching for a software based solution to a problem, trust clearly matters a great deal. Never is this more true that in the case of database backup software. After all, what good is a SQL Server database backup solution if you’re uncertain about the viability of the backups it creates?
In this article, we’re going go take a closer look at the transparent operational nature of SQL Backup Master, the company behind it, and your role in ensuring backup chain confidence.
SQL Backup Transparency
One of the best attributes of SQL Backup Master is that it isn’t doing anything magical or proprietary behind the scenes. SQL Backup Master leverages the native backup capabilities of SQL Server itself to create raw database backup files. It then compresses (and optionally encrypts) the backup using the compression algorithm of your choice (or none at all, if you prefer) and uploads it to your configured backup destination(s).
What this ultimately means it that you’re not required to use SQL Backup Master when it comes time to recover a database from backup. Of course, SQL Backup Master has a helpful database recovery option available to you – but it isn’t doing anything that you can’t do manually should you choose to do so. In fact, manual recovery is as simple as downloading the backup file from your backup destination, decompressing it using readily available tools, and then restoring the database directly via SQL Server (which can be done using SQL Server Management Studio or SQL statements).
I really can’t stress this point enough – SQL Backup Master adds considerable value to the SQL Server database backup process without stripping you of the freedom to adapt. And, just as importantly, database recovery process isn’t locked behind some expensive licensing paywall of a third party software company. Your data is your data, after all, and that’s how it should remain.
Pedigree of Key Metric Software
Another consideration when choosing a database backup solution is, of course, the software development company behind it.
I won’t dwell on this particular point for long, but I will note that Key Metric Software has been operational since 2003 and has a number of very well-regarded software products on the market – all actively developed and supported. We have over 30 thousand paid customers (plus many more using the free versions of some of our products) around the world – and our support services are world-class.
The goal of Key Metric Software has always been to create strong value propositions for organizations of all shapes and sizes, and SQL Backup Master is a critical part of that vision.
Your Role in Database Backup Security
I’ll conclude this article by stating that confidence in your SQL Server database backup and recovery strategy ultimately begins and ends with you.
When evaluating a database backup software solution, be sure to put the product through its paces by testing the entire backup chain from backup to recovery. You should also plan on re-testing the backup chain periodically to establish an ongoing sense of security and confidence.
After all, a database backup is only valuable if it can be recovered when needed.