sql | SQL‎ > ‎sql | Downloads‎ > ‎sql | Notes‎ > ‎

note.sqlsrv | 神奇 sql regread

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)

Comments