SQL Server Backup with Mirror Option in Maintenance Plan

backupjobssql server

I need to do backup ALL databases on an instance and put them in 3 different locations on our network, so i found that you can use backup with mirror, so I set that up.

I have a maintenance plan which runs a job (see job details below).
The problem I have is that if I add a database to the instance I will have to manually add it to the T-SQL job, is there a way I can avoid that.

I know you can have a step in the maintenance plan to backup all databases but you cant have it with the Mirror option (or can you?)

Can i get the maintenance plan to run my job on every database?

My job details: (I know there is only one database at the moment but I am just testing at the moment)

EXECUTE master.dbo.xp_create_subdir N'M:\Backup\DBA Tools'
    GO
    EXECUTE master.dbo.xp_create_subdir N'\\uhssql\sqlbak$\SQLDS\DBA Tools'
    GO
    EXECUTE master.dbo.xp_create_subdir N'\\sqlds02\backupprod$\Daily Backup\SQLDS\DBA Tools'
    GO

    DECLARE @BackupPathandDate VARCHAR(150)
    DECLARE @BackupPathandDateMirror VARCHAR(150)
    DECLARE @BackupPathandDateMirror2 VARCHAR(100)

    SET @BackupPathandDate = 'M:\Backup\DBATools_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100),':',''),' ','') + '.bak'
    SET @BackupPathandDateMirror = '\\uhssql\sqlbak$\SQLDS\DBATools_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100),':',''),' ','') + '.bak'
    SET @BackupPathandDateMirror2 = '\\sqlds02\backupprod$\Daily Backup\SQLDS\DBA Tools\DBATools_' + REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100),':',''),' ','') + '.bak'

    BACKUP DATABASE [DBA Tools] 
    TO DISK = @BackupPathandDate
    MIRROR TO DISK = @BackupPathandDateMirror
    MIRROR TO DISK = @BackupPathandDateMirror2
    WITH FORMAT

Many many thanks

Neil

Best Answer

Ola Hallengren automated backup solution will solve your problem for newly added databases.

Check for the MirrorDirectory option:

MirrorDirectory

Specify one or multiple directories to perform a mirrored backup.

The MirrorDirectory option in DatabaseBackup uses the MIRROR TO option in the SQL Server BACKUP command.

Related Question