USE [multiMAXTxnOps] GO /****** Object: StoredProcedure [dbo].[UP_DBA_BackupAllDatabases] Script Date: 05/30/2013 20:21:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[UP_DBA_BackupAllDatabases] AS DECLARE @StartBackup INT DECLARE @Msg varchar(50) DECLARE @NError INT DECLARE @Cmd varchar(300) DECLARE @BackupCmd VARCHAR(300) -- Always do the SQL Backup so that this resets the Checkpoints and allows auotshrink to free up unused space in the databases. backup database master to masterBackup with init SELECT @NError = @@ERROR SELECT @Msg = 'master backup' IF @NError <> 0 BEGIN RAISERROR (50005,11,-1,@Msg) WITH LOG END backup database model to modelBackup with init SELECT @NError = @@ERROR SELECT @Msg = 'model backup' IF @NError <> 0 BEGIN RAISERROR (50005,11,-1,@Msg) WITH LOG END backup database msdb to msdbBackup with init SELECT @NError = @@ERROR SELECT @Msg = 'msdb backup' IF @NError <> 0 BEGIN RAISERROR (50005,11,-1,@Msg) WITH LOG END backup database multimax to multiMaxBackup with init SELECT @NError = @@ERROR SELECT @Msg = 'multimax backup' IF @NError <> 0 BEGIN RAISERROR (50005,11,-1,@Msg) WITH LOG END -- shrink the database to try and keep it below the 2 Gb limit (MSDE only) dbcc shrinkdatabase (multiMAXTxn, 0) SELECT @NError = @@ERROR SELECT @Msg = 'multimaxtxn shrink' IF @NError <> 0 BEGIN RAISERROR (50005,11,-1,@Msg) WITH LOG END backup database multiMAXTxn to multiMAXTxnBackup with init SELECT @NError = @@ERROR SELECT @Msg = 'multimaxtxn backup' IF @NError <> 0 BEGIN RAISERROR (50005,11,-1,@Msg) WITH LOG END backup database multiMAXTxnOps to multiMAXTxnOpsBackup with init SELECT @NError = @@ERROR SELECT @Msg = 'txnops backup' IF @NError <> 0 BEGIN RAISERROR (50005,11,-1,@Msg) WITH LOG END DELETE FROM multiMAXTxnOps..BackupLogTable INSERT INTO multiMAXTxnOps..BackupLogTable(BackupLogID,BackupDte) VALUES (1,GETDATE()) IF (SELECT COUNT (*) FROM MultiMAX..BackupArchiveTable) = 1 BEGIN UPDATE MultiMAX..BackupArchiveTable SET LastBackupTime= GETDATE() END --If the backup is an IMMEDIATE backup (i.e. BackupArchiveTable..ImmediateBackup=1) --, then do not check whether the backup is set to run for the current day. IF (SELECT ImmediateBackup FROM MultiMAX..BackupArchiveTable) = 0 BEGIN IF @@DATEFIRST <> 7 SET DATEFIRST 7 IF (SELECT DATEPART(dw,GETDATE())) = 1 SET @StartBackup = (SELECT BackupOnSunday FROM MultiMAX..BackupArchiveTable ) IF (SELECT DATEPART(dw,GETDATE())) = 2 SET @StartBackup = (SELECT BackupOnMonday FROM MultiMAX..BackupArchiveTable ) IF (SELECT DATEPART(dw,GETDATE())) = 3 SET @StartBackup = (SELECT BackupOnTuesday FROM MultiMAX..BackupArchiveTable ) IF (SELECT DATEPART(dw,GETDATE())) = 4 SET @StartBackup = (SELECT BackupOnWednesday FROM MultiMAX..BackupArchiveTable ) IF (SELECT DATEPART(dw,GETDATE())) = 5 SET @StartBackup = (SELECT BackupOnThursday FROM MultiMAX..BackupArchiveTable ) IF (SELECT DATEPART(dw,GETDATE())) = 6 SET @StartBackup = (SELECT BackupOnFriday FROM MultiMAX..BackupArchiveTable ) IF (SELECT DATEPART(dw,GETDATE())) = 7 SET @StartBackup = (SELECT BackupOnSaturday FROM MultiMAX..BackupArchiveTable ) IF @StartBackup = 0 RETURN END DELETE FROM multiMAXTxnOps..BackupLogTable --Now set the ImmediateBackup Flag back to 0 , if it has been set. IF (SELECT ImmediateBackup FROM multiMAX..BackupArchiveTable ) =1 UPDATE multiMAX..BackupArchiveTable SET ImmediateBackup=0 --Return if no options have been set for backing up to tape , novadisk or novabackup. --This is determined by the value of BackupDevice that can be set as --N or NULL = don't backup --T = backup to NovaTape --X = backup to NovaDisk --B = backup to NovaBackup IF (select char(BackupDevice) from multimax..backuparchivetable) = 'N' BEGIN INSERT INTO multiMAXTxnOps..BackupLogTable(BackupLogID,BackupDte) VALUES (1,GETDATE()) UPDATE MultiMAX..BackupArchiveTable SET LastBackupTime= GETDATE() RAISERROR (50008,10,1) return END IF (select char(BackupDevice) from multimax..backuparchivetable) IS NULL BEGIN INSERT INTO multiMAXTxnOps..BackupLogTable(BackupLogID,BackupDte) VALUES (1,GETDATE()) IF (SELECT COUNT (*) FROM MultiMAX..BackupArchiveTable) = 1 BEGIN UPDATE MultiMAX..BackupArchiveTable SET LastBackupTime= GETDATE() END RAISERROR (50008,10,1) return END --Determine whether backup is to Tape,NovaDisk or NovaBackup if (select char(BackupDevice) from multimax..backuparchivetable) = 'T' begin exec master..xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Group4\MultiMAX\Backup', N'DeviceTBackupCommand', @BackupCmd OUTPUT select @BackupCmd select @cmd='exec master..xp_cmdshell ' +'''' +'"'+ @backupcmd + '"' +'''' select @cmd exec (@cmd) end if (select char(BackupDevice) from multimax..backuparchivetable) = 'X' begin DECLARE @NWBackupPath VARCHAR(1024) exec master..xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Group4\MultiMAX\Backup', N'DeviceXBackupCommand', @BackupCmd OUTPUT select @NWBackupPath = (SELECT NetworkBackupPath FROM multiMAX..SystemAttributeTable) --Check that only one row can be in here select @BackupCmd =@BackupCmd + '" ,"'+ @NWBackupPath +'"' SELECT @BackupCmd =( SELECT Replace (@BackupCmd,'("','(') ) SELECT @BackupCmd =( SELECT Replace (@BackupCmd,'")',')') ) select @cmd='exec master..xp_g4NovaDiskBackupInitialisation ' +'"'+ @backupcmd select @cmd exec (@cmd) DECLARE @BackupCmd2 VARCHAR(200) exec master..xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Group4\MultiMAX\Backup', N'DeviceXBackupCommand', @BackupCmd2 OUTPUT select @BackupCmd2 =@BackupCmd2 + '"' select @cmd='exec master..xp_cmdshell ' +'''' +'"'+ @backupcmd2 +'''' select @cmd exec (@cmd) end if (select char(BackupDevice) from multimax..backuparchivetable) = 'B' begin DECLARE @NWBackupPathNovaBack VARCHAR(1024) DECLARE @BackupDigitalVideoFiles VARCHAR(1) set @BackupDigitalVideoFiles = 'N' exec master..xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Group4\MultiMAX\Backup', N'DeviceBPath', @BackupCmd OUTPUT select @NWBackupPathNovaBack = (SELECT NetworkBackupPathNovaBack FROM multiMAX..SystemAttributeTable) IF (SELECT BackupDigitalVideoFiles FROM multiMAX..PreferencesTable ) =1 set @BackupDigitalVideoFiles = 'Y' --Check that only one row can be in here if (select char(WriteDevice) from multimax..backuparchivetable) = 'D' begin select @BackupCmd =@BackupCmd +''''+ ',' + '''' + @NWBackupPathNovaBack + '''' + ',' + '''' + 'D' + '''' + ',' + '''' + @BackupDigitalVideoFiles + '''' end if (select char(WriteDevice) from multimax..backuparchivetable) = 'T' begin select @BackupCmd =@BackupCmd +''''+ ',' + '''' + @NWBackupPathNovaBack + '''' + ',' + '''' + 'T' + '''' + ',' + '''' + @BackupDigitalVideoFiles + '''' end select @cmd='exec master..xp_g4NovaStorNovaBackupInitialisation ' + '''' + @backupcmd select @cmd exec (@cmd) --print @cmd DECLARE @BackupCmd3 VARCHAR(200) exec master..xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Group4\MultiMAX\Backup', N'DeviceBBackupCommand', @BackupCmd3 OUTPUT select @BackupCmd3 =@BackupCmd3 + '"' select @cmd='exec master..xp_cmdshell ' +'''' +'"'+ @backupcmd3 +'''' select @cmd exec (@cmd) --print @cmd end INSERT INTO multiMAXTxnOps..BackupLogTable(BackupLogID,BackupDte) VALUES (1,GETDATE()) IF (SELECT COUNT (*) FROM MultiMAX..BackupArchiveTable) = 1 BEGIN UPDATE MultiMAX..BackupArchiveTable SET LastBackupTime= GETDATE() END RAISERROR (50008,10,1) |