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