SQL Backup Master Blog

STEP 1 – GOOGLE CLOUD STORAGE ACCOUNT SETUP

To begin the creation of your Google Cloud Storage account, navigate to the Google Cloud Storage home page and select either Get started for free or Try Storage free. Google will then ask for company credentials, and a phone number to aid in the verification process. 

STEP 2 – CONNECT SQL SERVER DATABASE

Select the New Backup button in the Backup and Restore window of SQL Backup Master. In the Database Backup Editor window that appears, select Choose SQL Server. The Connect to SQL Server window will then be brought up.

In the Server name field, enter the name of the SQL Server instance that you are connecting to, or use the drop-down tab of the field to select one. Common examples include:

• (local)\SQLExpress – Specifies the default SQL Express named instance on the local computer.
• (local) – Specifies the local SQL Server installation (without an instance name).
• Server1\SQLExpress – Specifies the default SQL Express names instance on a remote computer named “Server1”.
• Server1 – Specifies an unnamed SQL Server instance on a remote computer named “Server1”.

Click on the button labeled Test SQL Connection to test your connection to the SQL Server, then select OK.

STEP 3 – SELECT DESIRED DATABASES TO BACK UP

Now that you’re connected to a SQL Server instance, the Database Backup Editor window will appear, displaying the available databases in the Source column. Select the database(s) you want to back up as part of this backup job.

The Back up all non-system databases check box is an option that allows the program to automatically back up all new databases created on the target SQL Server in the future.

STEP 4 – ADDING A BACKUP DESTINATION

Under the Destinations window header, select the Add icon. The window that is pulled up displays all compatible backup destinations for your SQL Database:

Choose Google Cloud Storage by double-clicking the selection box labeled Google Cloud Storage. 

The Google Cloud Storage Destination Settings window will then appear. This is where you establish and authorize a connection with your Google Cloud Storage. Input your Access key, Secret key, and Bucket into their respective text fields. Your Access key and Secret key can be generated at your project’s storage page of Google Cloud Storage.

Hit the Test button on the Google Cloud Storage Destination Settings window to make sure you have successfully connected to your Google account, then select Ok. 

Select the Ok button on the main window to continue. 

Back at the Database Backup Editor window, click on the Save icon to finalize your backup destination.

STEP 5 – RUN THE BACKUP

Back at the main window of the application, select your newly created backup job and click the Back up now button to begin the backup for your SQL Server Database to Google Cloud Storage.

If the backup job does not successfully complete, review the log file for further details.

STEP 1 – DROPBOX ACCOUNT CREATION AND SETUP

On the home page of Dropbox.com, select the Sign Up tab from the website’s top banner and input your personal credentials, select Create an account, then select the Dropbox package that best fits your needs. At this point in your account setup process, Dropbox will ask you a few questions to best tailor your experience, then bring you to the download page. Select Download Dropbox to begin the application’s installation process. 

STEP 2 – CONNECT TO SQL SERVER

In the Backup and Restore window of SQL Backup Master, click the New Backup button. In the Database Backup Editor window that appears, select the Choose SQL Server tab. The Connect to SQL Server window will be brought up.

In the field labeled Server name, select or enter the name of the SQL Server instance that you are connecting to. Common examples include:

• (local)\SQLExpress – Specifies the default SQL Express named instance on the local computer.
• (local) – Specifies the local SQL Server installation (without an instance name).
• Server1\SQLExpress – Specifies the default SQL Express names instance on a remote computer named “Server1”.
• Server1 – Specifies an unnamed SQL Server instance on a remote computer named “Server1”.

Another way to find local and remote SQL Server instances is by using the drop-down arrow of the Server name field. Finally, click the Test SQL Connection button to test your connection, then hit OK.

STEP 3 – SELECT DESIRED DATABASES TO BACK UP

After connecting to a SQL Server instance, the Database Backup Editor window will show available databases in the left-most column labeled Source. Select the databases you want to back up as part of this job.

You can also click the Back up all non-system databases check box. Enabling this option will back up all new databases created on the target SQL Server in the future automatically. 

STEP 4 – ADD A DROPBOX BACKUP DESTINATION

Select the Add button under the window header labeled Destinations. This will bring up a window displaying all of the possible backup destinations for your SQL Database:

Choose Dropbox by double-clicking the Dropbox selection box. 

The Dropbox Destination Settings window will then appear. This is where you authorize a connection to Dropbox. Select the Authorize Now tab and input your Dropbox login information on the new window. 

Signing in to your Dropbox account through the browser window will bring up the following window:

Next, you will be presented with a unique authentication code that you will need to continue verification. Copy this code with Ctrl+C and exit out of the browser window. 

Paste your authentication code into the text field of the newly created window:

Select Ok.

Use the Test button in the bottom left corner of the Dropbox Destination Settings window to make sure you have a successful connection to your Dropbox account, and select Ok, then hit the Ok button on the main window to continue. 

Back at the bottom right corner of the Database Backup Editor window, click on the Save icon to establish your backup destination.

STEP 5 – RUN THE BACKUP JOB

Back in the main window of the application, select your newly created backup job and hit the Back up now button to begin backing up to your Dropbox.

If the backup job does not complete successfully, please review the log file for further details on the failure.

Key Metric Software is proud to release version of 5.4 of our acclaimed SQL Backup Master software product.

This release includes powerful new capabilities, including SFTP (SSH File Transfer Protocol) private key file authentication, support for PowerShell scripts during custom batch script execution, and more.

We’ve also implemented a number of security improvements and bug fixes in this release, including support for obscuring access keys in backup destination configuration windows and the addition of backup destination module loading verification.

For details, please see the SQL Backup Master 4.5 release notes.

A new version of SQL Backup Master has arrived, and it delivers the often-requested ability to control database file paths during the recovery process. Let’s take a closer look.

I’ll start by launching the Backup Recovery Explorer, selecting a backup destination (an Amazon S3 bucket in this case), and then navigating to a test database that we’ll use for recovery demonstration purposes. I use the Action button to begin restoring a database from backup.

The Restore from Backup window appears, allowing me to configure the target database for the recovery operation. After clicking the Next button, SQL Backup Master will download the database backup and present me with a configurable list of database file paths.

I can now double-click a restore path to edit it directly, or use the associated folder icon to browse for a database file restore path. Finally, I click the Restore Now button to initiate the database backup recovery.

The latest release of SQL Backup Master is here. Version 5.2 provides official support for Windows 11 and Server 2022 along with a number of improvements and bug fixes.

Please see the release notes for details.

Key Metric Software has released SQL Backup Master v5.1.

This version includes a new dark theme, numerous performance improvements, HTML formatting for notification emails, and much more.

For a full review of what’s new, please see the product release notes.

Key Metric Software is proud to announce the general availability of SQL Backup Master 5. This new, major version offers a wide range of improvements and new features.

Learn about what’s new in SQL Backup Master v5.

We’re excited for you to get your hands on the new version. As always, we recommend testing your existing backup jobs after upgrading.

Existing standard or pro edition customers can upgrade to v5 at a 50% pricing discount (vs. new licenses).

Download SQL Backup Master v5 when you’re ready to upgrade.

If you encounter the above error while attempting to restore a differential backup, it usually indicates that it’s being applied to a full backup that isn’t the most recent one.

The most common cause we’ve seen is that a third-party program (or a SQL admin) is creating full backups in addition to the ones created by SQL Backup Master. And this means a more recent full backup likely exists somewhere about which SQL Backup Master has no knowledge.

In other words, a full backup was taken in between the full and differential backups being performed by SQL Backup Master. This results in a broken backup chain.

The very latest full backup must be located and restored prior to any differential what was derived from it. The differential cannot be restored to any prior backup (it must be the latest full backup that was created).

Diving deeper

If additional details are required, we recommend querying SQL Server’s backup set records. Doing so can help you identify where breaks in the backup chain may be occurring. Here’s a SQL query to get you started – you may wish to further restrict it by database name, time frame, etc.

SELECT 
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.database_backup_lsn,
msdb.dbo.backupset.differential_base_lsn,
CASE msdb..backupset.type
WHEN 'D' THEN 'Full'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Diff'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON
msdb.dbo.backupmediafamily.media_set_id =
msdb.dbo.backupset.media_set_id
ORDER BY 
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

Sample query results shown below demonstrate how to correlate a differential backup to a specific full backup. The magnified portion of the screenshot shows a differential backup with a differential_base_lsn field correlating to the first_lsn field of the full backup that precedes it.

The data provided by this query should help you to locate backup chain breaks. For additional information on querying this information from SQL Server, please see the link below.

backupset (Transact-SQL) – SQL Server | Microsoft Docs

Recommendations

The long-term solution to this problem is to ensure that only SQL Backup Master is creating SQL Server backups for your databases. Disable all other automated or scheduled SQL Server backup services for those databases, and use the SQL Backup Master GUI to create manual backups if needed.

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).

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.