Minion Backup: Restore to another server

Articles, Minion Backup, Tech / By

Minion Backup makes your SQL Server backups effortless, of course.  And even better, it makes your restores effortless! Oh, yes….yes indeed.

Today we’ll look at configuring a common, repeatable scenario: take the latest backup of MyDB from ProdServer1 and restore it to DevServer1. There are four basic steps to the setup and execution:

  1. Configure Minion Backup and let it run on ProdServer1. Restoring with MB requires at least one full backup taken by MB. (Note that you don’t need Minion Backup on DevServer1 for this scenario.)
  2. Configure restore settings paths. You know how sometimes a restore requires one or more “WITH MOVE” clauses?  Configure this once for your source-target pair, and MB takes care of it from then on.
  3. Configure the restore tuning settings (optional). Oh yes, we tune our backups AND our restores!
  4. Generate and run the restore statements.

Configure Minion Backup

If you’re not already set up with Minion Backup, you can download it and check out the Quick Start Guide (and/or the Minion Install Guide that comes with the download) to get started.

Once we have a full backup of MyDB, we’re ready to restore.

Configure restore settings paths

You know how sometimes a restore requires one or more “WITH MOVE” clauses?  Configure the target paths once for your source-target pair, and MB takes care of it from then on.

In our case, we’ll look at DevServer1 and see that all data files go on D:\SQLServer\, and all log files on L:\SQLServer\.

On ProdServer1, we configure these settings in the Minion.BackupRestoreSettingsPath table. We’ll insert one row for the log file destination, and one row for everything else (TypeName=’All’ here will cover mdf, ndf, filestream, and fulltext) :

INSERT INTO Minion.BackupRestoreSettingsPath ([DBName]
	,[ServerName]
	,[RestoreType]
	,[FileType]
	,[TypeName]
	,[RestoreDrive]
	,[RestorePath]
	,[RestoreFileName]
	,[RestoreFileExtension]
	,[BackupLocation]
	,[RestoreDBName]
	,[PathOrder]
	,[IsActive]
	,[Comment])
/* Settings for "All" files (except logs, because that's specified below): */
SELECT 'MinionDefault' AS [DBName]
	,'DevServer1' AS [ServerName]
	,'Full' AS [RestoreType]
	,'FileType' AS [FileType]
	,'All' AS [TypeName]
	,'D:\' AS [RestoreDrive]
	,'SQLServer\' AS [RestorePath]
	,'MinionDefault' AS [RestoreFileName]
	,'MinionDefault' AS [RestoreFileExtension]
	,'Backup' AS [BackupLocation]
	,'%DBName%' AS [RestoreDBName]
	,0 AS [PathOrder]
	,1 AS [IsActive]
	,'All restores to DevServer1 go to D:, and to L: (logs).' AS [Comment]
UNION
/* Settings for log files: */
SELECT 'MinionDefault' AS [DBName]
	,'DevServer1' AS [ServerName]
	,'Full' AS [RestoreType]
	,'FileType' AS [FileType]
	,'ldf' AS [TypeName]
	,'L:\' AS [RestoreDrive]
	,'SQLServer\' AS [RestorePath]
	,'MinionDefault' AS [RestoreFileName]
	,'MinionDefault' AS [RestoreFileExtension]
	,'Backup' AS [BackupLocation]
	,'%DBName%' AS [RestoreDBName]
	,0 AS [PathOrder]
	,1 AS [IsActive]
	,'All restores to DevServer1 go to D:, and to L: (logs).' AS [Comment]

There are several things of note here:

  • DBName = ‘MinionDefault’ means that all databases for this server will receive these settings.
  • Hierarchy: As with all Minion Maintenance modules, there’s a settings hierarchy* at work. You can specify settings with DBName=MinionDefault for DevServer1, for example, and then override that for a specific database using DBName=databasename.
  • RestoreType must equal Full. Remember, we don’t need “WITH MOVE” statements for Log or Diff restores!
  • You can restore the source database to a different name by setting RestoreDBName. I do not advise you to do this on DBName = MinionDefault rows!  (See, because any database you restore to that server would have that specific database name…)

There’s a lot more we could say about this, but I’ll just encourage you to read the Minion.BackupRestoreSettingsPath documentation.

*For more on the settings hierarchy, see Architecture Overview.

Configure restore tuning settings

While this step is optional, I highly encourage you to study Sean’s Backup Tuning class* (here is the demo code), so that you fully understand the backup tuning process.

Just as an example, let’s say we have done our due diligence, and tuned restores on the target system. We might use the following restore tuning settings:

ServerName DBName RestoreType SpaceType ThresholdMeasure ThresholdValue Buffercount MaxTransferSize BlockSize Replace WithFlags
MinionDefault MinionDefault All DataAndIndex GB 0 0 0 0 0 STATS=1
MinionDefault MinionDefault All DataAndIndex GB 20 100 2097152 0 0 STATS=1
MinionDefault MinionDefault All DataAndIndex GB 50 200 2097152 0 0 STATS=1
MinionDefault MinionDefault All DataAndIndex GB 100 400 2097152 0 0 STATS=1
MinionDefault MinionDefault All DataAndIndex GB 150 500 2097152 0 0 STATS=1

I’ll give an example insert statement below, but first a few things of note:

  • ServerName = ‘MinionDefault’ means these settings apply to restores targeted at any server.
  • Just like before, DBName = ‘MinionDefault’ means that these settings apply to any database we’d like to restore.
  • ThresholdValue is what makes these settings dynamic. As a database grows past a threshold, it qualifies for the next set of restore settings. So, a 22 GB database will be restored using a BufferCount of 100, and so on. IMPORTANT: Always specify a ThresholdValue = 0 row!
  • We specified Replace=0, which means if MyDB exists on the target server already, the restore will fail (or, you know, we can add “WITH REPLACE” to the restore statement). Use Replace=1 at your own risk!

WARNING: If you choose to specify buffercount and maxtransfersize without going through the actual tuning process, you could get into trouble! It’s fairly easy to use up all the resources on a system with these settings. While that might be fine during off hours, over-tuned restores performed during peak periods could interfere badly with the system performance (on the target server).

Here is an example insert that gives you that first row:

INSERT INTO Minion.BackupRestoreTuningThresholds ([ServerName]
	, [DBName]
	, [RestoreType]
	, [SpaceType]
	, [ThresholdMeasure]
	, [ThresholdValue]
	, [Buffercount]
	, [MaxTransferSize]
	, [BlockSize]
	, [Replace]
	, [WithFlags]
	, [IsActive])
SELECT 'MinionDefault' AS [ServerName]
	, 'MinionDefault' AS [DBName]
	, 'All' AS [RestoreType]
	, 'DataAndIndex' AS [SpaceType]
	, 'GB' AS [ThresholdMeasure]
	, 0 AS [ThresholdValue]
	, 0 AS [Buffercount]
	, 0 AS [MaxTransferSize]
	, 0 AS [BlockSize]
	, 1 AS [Replace]
	, 'STATS=1' AS [WithFlags]
	, 1 AS [IsActive];

 

* Note that a lot of the advance work Sean does in that class, is actually done FOR YOU by Minion Backup. For example, MB will tell you, in the log, what buffercount and MaxTransferSize SQL chose to use; you don’t have to go looking for it yourself!

Generate and run restore statements

Now that you’ve done your configuration, from here on it will be VERY simple to generate full, differential, and log restore statements! Let’s generate our full restore for MyDB first:

exec Minion.BackupRestoreDB
	  @ServerName = 'DevServer1'
	, @DBName='MyDB'
	, @BackupType='Full'
	, @BackupLoc='Primary'
	, @StmtOnly=1;

MyDB is a 40GB database, which was originally backed up to 10 files. So the restore statement generated is:

RESTORE DATABASE MyDB FROM
DISK = '\\share1\backups\ProdServer1\01of10_MyDB_Full20180628092131.BAK',
DISK = '\\share1\backups\ProdServer1\02of10_MyDB_Full20180628092131.BAK',
DISK = '\\share1\backups\ProdServer1\03of10_MyDB_Full20180628092131.BAK',
DISK = '\\share1\backups\ProdServer1\04of10_MyDB_Full20180628092131.BAK',
DISK = '\\share1\backups\ProdServer1\05of10_MyDB_Full20180628092131.BAK',
DISK = '\\share1\backups\ProdServer1\06of10_MyDB_Full20180628092131.BAK',
DISK = '\\share1\backups\ProdServer1\07of10_MyDB_Full20180628092131.BAK',
DISK = '\\share1\backups\ProdServer1\08of10_MyDB_Full20180628092131.BAK',
DISK = '\\share1\backups\ProdServer1\09of10_MyDB_Full20180628092131.BAK'
WITH  Move 'mydb' TO 'S:\SQLServer\mydb.mdf'
	, Move 'mydb_log' TO 'L:\SQLServer\mydb_log.LDF'
	, BufferCount = 100
	, MaxTransferSize = 2097152
	, REPLACE
	, STATS=1;

That worked beautifully!

  • We automatically got the most recent full backup,
  • And generated MOVE clauses for our target server,
  • And all the restore settings we wanted!

As long as we’re at it, we may as well add “NORECOVERY” to the WITH clause above, and generate all of the necessary log restore statements:

exec Minion.BackupRestoreDB
	  @ServerName = 'DevServer1'
	, @DBName='MyDB'
	, @BackupType='Log'
	, @BackupLoc='Primary'
	, @StmtOnly=1;

MB is smart enough to provide you with just the log restores since the last full backup.

Take all those restore statements, and run them against DevServer1*.

That’s all there is to it! Restoring any database to DevServer1 will be as easy as exec Minion.BackupRestoreDB from here on

 

*We don’t automatically run your restore statements against the target – even if @StmtOnly = 0 – because, well, that seems like an easy way to get into a LOT of trouble. RESTORE … WITH REPLACE is one powerful piece of technology, friends.

And More

You can certainly automate your restores, if you like, using Minion Backup and a little bit of PowerShell. But even if you don’t go that route, we hope you’ll find the restore features as delightful as we do.

Meanwhile, more resources!