Backup Pre and Post Script

[Nel40]

We have just completed a new install of Enterprise Vault and I am in the process of setting up our Backup.  We use BackupExec 11d, I have created the following Pre and Post Scripts and althought they work would like to make sure this is the best way of backing up EV.  Any advice will be much appreciated.

My Pre-Backup.bat File

@echo off

rem ///////////////////////////////////////////////////////////////////
rem / Pre-Backup script to place Enterprise Vault into read-only mode /
rem ///////////////////////////////////////////////////////////////////

C:
cd "\Program Files\Enterprise Vault\EVScripts"

rem ///////////////////////////////
rem / Stop all Tasks             /
rem ///////////////////////////////

net stop /y "Enterprise Vault Task Controller Service"

rem ////////////////////////////
rem / Stop the Storage Service /
rem ////////////////////////////

net stop /y "Enterprise Vault Storage Service"

rem //////////////////////////
rem / Stop the Index Service /
rem //////////////////////////

net stop /y "Enterprise Vault Indexing Service"

rem //////////////////////////
rem / Stop the Shopping Service /
rem //////////////////////////

net stop /y "Enterprise Vault Shopping Service"

rem //////////////////////////////////////////////
rem / Place Enterprise Vault into Read-Only mode /
rem //////////////////////////////////////////////

reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableArchive /t REG_DWORD /d 00000000 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableExpiry /t REG_DWORD /d 00000000 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableFileWatch /t REG_DWORD /d 00000000 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableReplayIndex /t REG_DWORD /d 00000000 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableRestore /t REG_DWORD /d 00000000 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnablePSTMigrations /t REG_DWORD /d 00000000 /f

rem /////////////////////////////////////////////////////
rem / Start the Storage, Shopping and Indexing Services/
rem ////////////////////////////////////////////////////

net start "Enterprise Vault Storage Service"
net start "Enterprise Vault Indexing Service"
net start "Enterprise Vault Shopping Service"
net start "Enterprise Vault Task Controller Service"

rem ///////////////////////////////////////////////
rem / Dump all Enterprise Vault Databases to Disk /
rem ///////////////////////////////////////////////
 
osql -S localhost -i "c:\Program Files\enterprise vault\EVScripts\dumpdb.sql" -E

My Dumpdb.sql File

-- automated Dump procedure
-- dumps all databases in .bak files

use EnterpriseVaultDirectory

GO

Declare @BackupDir varchar(255)

-- CHANGE THIS IF NECESSARY

-- Set='\\UNC location of SQL backup dir\...(see eg below)'
Set @BackupDir='\\vault01\SQLBackup$\'

-- Use a Cursor to loop thru the Directory
-- in order to find all DB's

DECLARE dbcursor CURSOR FOR
select DatabaseName
from   dbo.VaultStoreEntry

OPEN dbcursor

declare @dbname varchar(255)
declare @s varchar(255)

-- Perform the first fetch.
FETCH NEXT FROM dbcursor into @dbname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   print ''
   print 'Backing up : ' + isnull(@dbname,'noname')

   -- create a Backup device called 'temp' with type of disk
   -- and Filename DBNAME.bak

   set @s = @BackupDir + isnull(@dbname,'noname') + '.bak'
   EXEC sp_addumpdevice 'disk', 'xtemp', @s

   -- Truncate the logfile
   print ''
   print 'Truncating log'
   BACKUP LOG @dbname WITH TRUNCATE_ONLY
   print 'Truncated log'
   print ''
   -- Dump the DB into that file

   BACKUP DATABASE @dbname
   TO xtemp
   WITH  DESCRIPTION = 'Enterprise Vault Backup', INIT

   -- drop the device

   EXEC sp_dropdevice 'xtemp'

   -- This is executed as long as the previous fetch succeeds.

   FETCH NEXT FROM dbcursor into @dbname
END

-- close and unload everything

CLOSE dbcursor
DEALLOCATE dbcursor

-- Backup the Directory Database

USE MASTER

print ''
print 'Backing up : Enterprise Vault Directory Database'

set @s = @BackupDir + 'EnterpriseVaultDirectory.bak'
EXEC sp_addumpdevice 'disk', 'xtemp', @s

   -- Truncate the logfile
   print ''
   print 'Truncating log'
   BACKUP LOG EnterpriseVaultDirectory WITH TRUNCATE_ONLY
   print 'Truncated log'
   print ''

-- Dump the DB into that file

BACKUP DATABASE EnterpriseVaultDirectory
TO xtemp
WITH  DESCRIPTION = 'Enterprise Vault Backup', INIT

-- drop the device

EXEC sp_dropdevice 'xtemp'

-- Backup the Monitoring Database

USE MASTER

print ''
print 'Backing up : Enterprise Vault Monitoring Database'

set @s = @BackupDir + 'EnterpriseVaultMonitoring.bak'
EXEC sp_addumpdevice 'disk', 'xtemp', @s

   -- Truncate the logfile
   print ''
   print 'Truncating log'
   BACKUP LOG EnterpriseVaultMonitoring WITH TRUNCATE_ONLY
   print 'Truncated log'
   print ''

-- Dump the DB into that file

BACKUP DATABASE EnterpriseVaultMonitoring
TO xtemp
WITH  DESCRIPTION = 'Enterprise Vault Backup', INIT

-- drop the device

EXEC sp_dropdevice 'xtemp'

-- Backup the Master Database

USE MASTER

print ''
print 'Backing up : Master Database'

set @s = @BackupDir + 'Master.bak'
EXEC sp_addumpdevice 'disk', 'xtemp', @s

   -- Truncate the logfile
   print ''
   print 'Truncating log'
   BACKUP LOG Master WITH TRUNCATE_ONLY
   print 'Truncated log'
   print ''

-- Dump the DB into that file

BACKUP DATABASE Master
TO xtemp
WITH  DESCRIPTION = 'Enterprise Vault Backup', INIT

-- drop the device

EXEC sp_dropdevice 'xtemp'

-- Backup the MSDB Database

USE MASTER

print ''
print 'Backing up : MSDB Database'

set @s = @BackupDir + 'MSDB.bak'
EXEC sp_addumpdevice 'disk', 'xtemp', @s

   -- Truncate the logfile
   print ''
   print 'Truncating log'
   BACKUP LOG MSDB WITH TRUNCATE_ONLY
   print 'Truncated log'
   print ''

-- Dump the DB into that file

BACKUP DATABASE MSDB
TO xtemp
WITH  DESCRIPTION = 'Enterprise Vault Backup', INIT

-- drop the device

EXEC sp_dropdevice 'xtemp'

My Post-Backup.bat File

@echo off

rem /////////////////////////////////////////////////////////////////
rem / Post-Backup script to place Enterprise Vault into Normal mode /
rem /////////////////////////////////////////////////////////////////

C:
cd \Program Files\Enterprise Vault\EVScripts

rem ////////////////////////////
rem / Stop all Tasks        /
rem ////////////////////////////

net stop /y "Enterprise Vault Task Controller Service"

rem ////////////////////////////
rem / Stop the Storage Service /
rem ////////////////////////////

net stop /y "Enterprise Vault Storage Service"

rem //////////////////////////
rem / Stop the Index Service /
rem //////////////////////////

net stop "Enterprise Vault Indexing Service"

rem //////////////////////////
rem / Stop the Shopping Service /
rem //////////////////////////

net stop /y "Enterprise Vault Shopping Service"

rem //////////////////////////////////////////////
rem / Place Enterprise Vault into Normal mode /
rem //////////////////////////////////////////////

reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableArchive /t REG_DWORD /d 00000001 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableExpiry /t REG_DWORD /d 00000001 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableFileWatch /t REG_DWORD /d 00000001 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableReplayIndex /t REG_DWORD /d 00000001 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableRestore /t REG_DWORD /d 00000001 /f
reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnablePSTMigrations /t REG_DWORD /d 00000001 /f

rem /////////////////////////////////////////////////////////////////////
rem / Copy the IgnoreArchiveBitTrigger.txt to the Vault Store Partition /
rem /////////////////////////////////////////////////////////////////////

rem If the archive bit is not supported in the backup software uncomment and d-click the IgnoreArchiveBitTrigger.reg
rem copy "c:\program files\Enterprise Vault\EVScripts\IgnoreArchiveBitTrigger.txt" rem "E:\EVStorage\Vault Store\Mailboxes Ptn1"

rem ///////////////////////////////////////////
rem / Start the Storage, shopping Indexing Services /
rem ///////////////////////////////////////////

net start "Enterprise Vault Storage Service"
net start "Enterprise Vault Indexing Service"
net start "Enterprise Vault Shopping Service"

rem ////////////////////////////////////
rem / Start all Tasks          /
rem ////////////////////////////////////

net start "Enterprise Vault Task Controller Service"

[Michael_Bilsbor]

Hi,

I wouldn't both with this registry key

reg add "HKLM\SOFTWARE\KVS\Enterprise Vault\Storage" /v EnableRestore /t REG_DWORD /d 00000000 /f

As it means users won't be able to restore (which you might want them too) and importantly users won't be able to view items via OWA during 'read-only' mode otherwise.

Cheers,

Mike

[Nel40]

Mike

Will remove this key from the script.

Many Thanks

Nel

[Nel40]

Mike

Wonder if I can get your thoughts on the following Warning Message please EventID 41013, it is relating to the way our EV Backup Process was setup?

The SQL database transaction log for Vault Store 'Mailbox' has used 93% of its allocated space.

The information in this database is at risk until the database has been backed up.

Review your SQL database backup procedures and make any changes needed to ensure that backups happen in a timely fashion.

Ensure that your database is adequately sized for the transactions taking place during an archive run.

Also check that there is enough disk space available to accommodate any growth in your database and database transaction log files.

Nel

[Liam_Finn1]

Nel,

I was wondering why you do your SQL backups to file via a batch file instead of doing it through SQL maintenance plan.

We do it through a maintenance plan for our .BAK files of each of our databases and we also truncate out our transaction logs to file every 15 minutes and we find this to be very fast and efficient.

Just curious why you do it in a batch file

Liam

[Nel40]

Liam

Not my choice would prefer to use SQL but our EV was implemented by an external consultant that for some reason setup this method, since I am new to EV and still learning on the Job didn’t wanted to upset things just yet but I am starting to think I should just follow my instinct on this one.... from what you are saying looks like SQL does the job better and this is the method I normally us on all my other SQL Servers!!

Nel

[Liam_Finn1]

Nel,

I'm not saying once is better than the other. I just think that if you let SQL manage it's self and do what it was designed to do rather than implement something from the outside then logic says that it will be easier to restore if there is a failure. This is why i use native SQL jobs for this

Personally I dont perform my backups of everything at the same time but thats a choice I made

I backup my SQl to .bak every morning at 8AM

I truncate my SQL logs to file every 15 minutes to file

I backup what i put to file once a day to tape and then delete the files so i dont flood my disk space and thats at 1PM every day

I backup my indexes every morning at 5am once my FSA runs have completed so the only info not in the backup is

i put my services into maintainance mode so it does not affect my users

I have had system failures but the most i would miss from my SQL is 15 mins of data. the most i miss from my indexes is 1 day and i can rebuild those once i have my database so my risk is very small

[Nel40]

Liam

Sounds good to me.

Many Thanks

Nel