Print ''
Print '-------------------------------------------------------------------------'
Print ' Create JobMgr: Database Backup File'
Print ' Autor        : J.Feuerstein'
Print ' FirstRevision: V2.0.0   12.07.2015'
Print ' CurrRevision : V2.0.2   11.09.2023 Check when SQLExpress is used'
Print '-------------------------------------------------------------------------'

declare @ExecuteSQLstr nvarchar(max)
declare @DBBackup_DP nvarchar(max),
        @DBBackup_N nvarchar(max),
        @DBBackup_NE nvarchar(max),
        @DBBackup_DPNE nvarchar(max),
        @DBBackup_NameDescription nvarchar(max),
        @ExecuteSQL nvarchar(max),
        @Msg nvarchar(max)

set @DBBackup_N='#DB_N#'
set @DBBackup_DP='#DB_Backup_DP#'
set @DBBackup_NE=@DBBackup_N  + '_#DB_Backup_TimeStamp#.DbBackup'
set @DBBackup_DPNE = @DBBackup_DP + '\' + @DBBackup_NE
set @DBBackup_NameDescription = @DBBackup_N + N' - Full Database Backup'

-- Doc:11.09.2023/J.Fes + A.Becker add  COMPRESSION,
set @ExecuteSQL =
 'BACKUP DATABASE [' + @DBBackup_N + '] ' +
 'TO DISK = ''' + @DBBackup_DPNE + ''' WITH  COPY_ONLY, ' +
 'RETAINDAYS = 1, ' +
 'NOFORMAT, INIT, COMPRESSION, ' +
 'NAME = ''' + @DBBackup_NameDescription + ''',' +
 'SKIP, NOREWIND, NOUNLOAD, STATS = 10, CONTINUE_AFTER_ERROR'
set @Msg = 'BACKUP DATABASE [' + @DBBackup_N + '] TO DISK = ...'

Print '-------------------------------------------------------------------------'
print ('DB......: ' + @DBBackup_N)
print ('to dir..: ' + @DBBackup_DP)
print ('to file.: ' + @DBBackup_NE)
print ('via.....: ' + @Msg)

exec (@ExecuteSQL)
print ('Backup..: ' + 'Done')
Print '.........................................................................'
print ('Result..: ' + 'Check')
/*
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'#JobManagerV2_WorkInst#'
       and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'#JobManagerV2_WorkInst#')

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''#JobManagerV2_WorkInst#'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'D:\SQL-DATA\MSSQL11.MSSQLSERVER\MSSQL\Backup\#JobManagerV2_WorkInst#.DBBackup' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
*/

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=@DBBackup_N
       and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@DBBackup_N)

print ('Result..: ' + 'Check ... Done')
Print '.........................................................................'

if @backupSetId is null
begin
  print ('Result..: ' + 'Verify failed')
  set @Msg = N'ERROR:Verify failed. Backup information for database:' + @DBBackup_N + N' not found.'
  raiserror(@Msg, 16, 1)
end
else
begin
  print ('Result..: ' + 'Verify OK')
  print ('Action..: ' + 'RESTORE VERIFYONLY FROM DISK ..')
  -- set @ExecuteSQL = 'RESTORE VERIFYONLY FROM  DISK = ''' + @DBBackup_DPNE '' +
  -- RESTORE VERIFYONLY FROM DISK = @DBBackup_DPNE  WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
end
-- RESTORE VERIFYONLY FROM  DISK = N'D:\SQL-DATA\MSSQL11.MSSQLSERVER\MSSQL\Backup\#JobManagerV2_WorkInst#.DBBackup' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

