3

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

1

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.

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.