Customized Solutions Using SQL Safe Backup’s XSPs

During my days as a Technical Support Engineer here at IDERA, I’d occasionally encounter a customer trying to do something that’s not directly available in the SQL Safe Management Console and/or the SQL Safe Web Console. Some of these things that were possible through the use of SQL Safe Backup’s Extended Stored Procedures (XSPs) and/or SQL Safe Backup’s Command Line Interface (CLI).

Let’s say we have a SQL Safe Backup user named Ducky. Ducky is an average SQL Server DBA, whose responsibilities include maintaining database performance, running reports for upper management, backup and recovery, and much more. Given the number of responsibilities, Ducky likes to automate as many tasks as possible through scripts, and this is where SQL Safe Backup’s CLIs and XSPs can be used.

Ducky knows that recovery of the databases that he manages must be performed as quickly as possible. He also knows that the need to restore a database can happen at any time of the day. It doesn’t matter if the database becomes corrupted during work hours or at 2:54 AM while he’s dreaming of all the various IDERA ducks in his collection. To limit downtime, Ducky decides to take a semi-automated approach.

In SQL Safe Backup, the normal method of restoring a database involves a number of mouse clicks to define the restore operation that is to be performed. This involves selecting the backup files, defining the account used to access the backup files, and many other settings. It really doesn’t take that long to start a restore operation this way, but if it allows Ducky to get back to bed a few minutes sooner, it’s worth the effort for him. So Ducky gets to work at his automation script.

I’ll be the first to admit that I’m not a DBA by any means. Given that, I’m sure the script that I’m about to sample below could be written much better. I just want to demonstrate one use of the SQL Safe Backup’s XSPs to show how it can be used to customize tasks. Just as any other scripts you find online, use at your own risk. This script is to demonstrate what the possibilities of what can be done with the SQL Safe Backup XSPs.

USE msdb
GO

/*

The following are variable that must be adjusted to your environment and the database that you'd like to restore.

*/
DECLARE @DatabaseName NVARCHAR(128) = N'MyDatabaseName'
DECLARE @DiscUsers INT = 1
DECLARE @Replace INT = 1
/* Access File Sytem Info */
-- DECLARE @WindowsUsername nvarchar(255) = N'MYDOMAINservice_acct'
-- DECLARE @EncryptedWindowsPassword nvarchar(255) = N'E7RG2X9WIXzXlc/v/00x6wjantcWbQ'
IF OBJECT_ID('tempdb..#BackupList') IS NOT NULL
DROP TABLE #BackupList;

CREATE TABLE #BackupList(rowid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, physical_device_name NVARCHAR(260), type CHAR(1), backup_start_date datetime, done_loop BIT DEFAULT(0));

-- Find latest FULL backup file.
INSERT INTO #BackupList(physical_device_name, type, backup_start_date)
SELECT TOP 1 bmf.physical_device_name, bs.type, bs.backup_start_date FROM msdb.dbo.backupmediafamily AS BMF
INNER JOIN msdb.dbo.backupset AS BS ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = @DatabaseName AND bs.type = N'D'
ORDER BY bs.backup_start_date DESC

-- Find latest DIFF backup file.
INSERT INTO #BackupList(physical_device_name, type, backup_start_date)
SELECT TOP 1 bmf.physical_device_name, bs.type, bs.backup_start_date FROM msdb.dbo.backupmediafamily AS BMF
INNER JOIN msdb.dbo.backupset AS BS ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = @DatabaseName AND (bs.type = N'I' AND bs.backup_start_date > (SELECT backup_start_date FROM #BackupList WHERE type=N'D') OR bs.type = N'L' AND bs.backup_start_date > (SELECT backup_start_date FROM #BackupList WHERE type=N'I'))
ORDER BY bs.backup_start_date DESC

DECLARE @finishdate datetime

IF EXISTS (SELECT 1 FROM #BackupList WHERE type=N'I')
SET @finishdate = (SELECT backup_start_date FROM #BackupList WHERE type=N'I')
ELSE
SET @finishdate = (SELECT backup_start_date FROM #BackupList WHERE type=N'D')

-- Find all LOG backup files preceeding the most recent DIFF backup.
INSERT INTO #BackupList(physical_device_name, type, backup_start_date)
SELECT bmf.physical_device_name, bs.type, bs.backup_start_date FROM msdb.dbo.backupmediafamily AS BMF
INNER JOIN msdb.dbo.backupset AS BS ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name =@DatabaseName AND bs.type = N'L' AND bs.backup_start_date > @finishdate
ORDER BY bs.backup_start_date ASC

-- THE LINE BELOW REPORTS THE BACKUP FOUND. IT CAN BE COMMENTED OUT.
-- SELECT * FROM #BackupList
IF EXISTS(SELECT 1 FROM #BackupList)
BEGIN
-- Build the restore command using SQL Safe Backup's XSPs
DECLARE @BackupFileName NVARCHAR(260)
DECLARE @DynamicSQL NVARCHAR(max) = 'DECLARE @ResultCode INT' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
WHILE EXISTS(SELECT 1 FROM #BackupList WHERE done_loop=0)
BEGIN
SET @BackupFileName = (SELECT TOP 1 physical_device_name FROM #BackupList WHERE done_loop=0 ORDER BY rowid ASC)
SET @DynamicSQL = @DynamicSQL + CHAR(13) + CHAR(10) + 'EXEC @ResultCode = master.dbo.xp_ss_restore @database = ''' + @DatabaseName + ''', @filename = ''' + @BackupFileName + ''', @replace = ' + CAST(@Replace AS nvarchar) + ', @disconnectusers = ' + CAST(@DiscUsers AS nvarchar) /*Access File System Info*/ -- + ', @WindowsUsername = ''' + @WindowsUsername + ''', @EncryptedWindowsPassword = ''' + @EncryptedWindowsPassword + ''''

UPDATE #BackupList SET done_loop=1 WHERE physical_device_name=@BackupFileName
IF EXISTS(SELECT 1 FROM #BackupList WHERE done_loop=0)
SET @DynamicSQL = @DynamicSQL + ', @RecoveryMode = ''norecovery''' + CHAR(13) + CHAR(10) + 'IF(@ResultCode != 0) RAISERROR(''One or more operations failed to complete.'', 16, 1);' + CHAR(13) + CHAR(10)
ELSE
SET @DynamicSQL = @DynamicSQL + CHAR(13) + CHAR(10) + 'IF(@ResultCode != 0) RAISERROR(''One or more operations failed to complete.'', 16, 1);' + CHAR(13) + CHAR(10)
END
EXECUTE sp_executesql @DynamicSQL -- Executes the backup operations.
END
ELSE
PRINT 'There are no backups to be restored.'

DROP TABLE #BackupList;

The script above has a number of comments within it. For a quick summary, it checks the msdb database to find the latest full backup, the most recent differential backup that was made after the full backup, and all preceding log backup files. Next, it then builds the T-SQL script calling the XSPs to perform the restores and then executes it. Now that Ducky has this script written, he can quickly restore a database to the latest state based on the backups that were made.

The story told above is just one example of how the SQL Safe Backup XSPs can be used to customize some operations. I’m certain that there are most possibilities in regards to how these can be used.

If you haven’t take a look at SQL Safe Backup before, check out our product page here!

Are you using the SQL Safe Extended Stored Procedures? If so, share the details of how you’re using them! I’m certainly interested and I’m sure others will be too!