0

I am using Ola Hallengren's Maintenance Solution, and it's been working great for our environment.

Quick question regarding the User Database backups s proc. Currently, when I'm taking backups, the backup files are getting saved to one of the servers local drive (Z: drive). But if I wanted to also get another copy of those same backup sets to a different drive or folder, I was thinking the @MirrorDirectory is where we can enter a different drive path for the backup files. (Please correct me if I'm wrong).

I wanted to test this, so I have duplicated the original the User Backup Database sqlserver job for my main backup job and renamed this new sqlserver job a "DatabaseBackup - USER_DATABASES - FULL_COPYONLY". Here's what I have on the first and only job step:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LSDBA_DB -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'Z:\COPYONLY_BACKUPS',@MirrorDirectory =N'C:\MIRRORBAKS', @CopyOnly = 'Y', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 192, @MirrorCleanupTime= 24, @CheckSum = 'Y', @LogToTable = 'Y'" -b

I'm currently testing this in the hope that it will place the main backup files on "Z:\COPYONLY_BACKUPS" folder, and place another backup set on "C:\MIRRORBAKS" foder. (I'm place them in C drive just for testing).

When I run the job, it is failing and the message is: Msg 50000, Level 16, State 1, Server (Instance Name here), Procedure DatabaseBackup, Line 436 The value for the parameter @MirrorDirectory is not supported. Msg 50000, Level 16, State 1, Server (Instance Name here), Procedure DatabaseBackup, Line 740 The documentation is available at https://ola.hallengren.com/sql-server-backup.html. Date and time: 2018-11-19 11:05:58. Process Exit Code 1. The step failed.

Could anyone shed some light on why it is saying the parameter for @MirrorDirectory is not supported? Thank you very much.

0

The backup procedure does a number of validation checks against the path to ensure it is valid. One of them checks the edition of SQL Server to ensure it can perform mirrored backups.

Run the below code and if it does not return 3 then you don't have Enterprise Edition and cannot use Mirrored Backups:

SELECT SERVERPROPERTY('EngineEdition')

See: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/mirrored-backup-media-sets-sql-server?view=sql-server-2017

  • Thank you Handy. I didn't realize mirrored backup media sets are only supported on Enterprise Edition. We have Standard Edition. – Schmallen Nov 20 '18 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.