vbscript, scripts, postgresql,batch file, automate, windows 2008

Hi experts..

I running the below script everyday using task scheduler to backup the database locally , and save along with the current date and time.
I need your help how to enhance little bit to rotate the backup copy, keep the latest copy and delete the previous copies.

set PGPASSWORD=password123
@echo off
set datetime=%date:~-4%_%date:~-7,2%_%date:~-10,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%
echo %datetime%
"D:\Program Files\VASCO\PostgreSQL\bin\pg_dump" -f "D:\Program Files\VASCO\IAS-Backup\DB_BK\PRI_AUTH_DB_BAK_%datetime%.bak" -Fc -Z9 -U username -v databaseinstance


please help me.
Skumar_CCSAAsked:
Who is Participating?
 
Bill PrewCommented:
Also, consider using variables to reduce typing, make maintenance and changes easier, and improve readability.  Here's how I might approach it:

@echo off

REM Set paths and database info
set PGPASSWORD=password123
set PGuser=username
set PGinstance=databaseinstance
set PGdump=D:\Program Files\VASCO\PostgreSQL\bin\pg_dump
set BackupDir=D:\Program Files\VASCO\IAS-Backup\DB_BK
set BackupName=DB_BK\PRI_AUTH_DB_BAK_

REM Build a datetime stamp
set DateTime=%DATE:~-4%_%DATE:~-7,2%_%DATE:~-10,2%_%TIME:~0,2%_%TIME:~3,2%_%TIME:~6,2%
echo %DateTime%

REM Backup now
"%PGdump%" -f "%BackupDir%\%BackupName%%DateTime%.bak" -Fc -Z9 -U %PGuser% -v %PGinstance%

REM Only keep 10 newest files in backup folder
for /F "tokens=* skip=10" %%A in ('dir /b /a-d /tc /o-d "%BackupDir%\%BackupName%*.bak"') do del "%BackupDir%\%%~A"

Open in new window

~bp
0
 
Bill PrewCommented:
Here's a basic addition to your script (I'm assuming it works correctly as you showed it) to add a step to only keep a certain number of backups.  Typically you want more than 1, I chose to keep 10 for the example.

@echo off

REM Set password
set PGPASSWORD=password123

REM Build a datetime stamp
set datetime=%date:~-4%_%date:~-7,2%_%date:~-10,2%_%time:~0,2%_%time:~3,2%_%time:~6,2%
echo %datetime%

REM Backup now
"D:\Program Files\VASCO\PostgreSQL\bin\pg_dump" -f "D:\Program Files\VASCO\IAS-Backup\DB_BK\PRI_AUTH_DB_BAK_%datetime%.bak" -Fc -Z9 -U username -v databaseinstance

REM Only keep 10 newest files in backup folder
for /F "tokens=* skip=10" %%A in ('dir /b /a-d /tc /o-d "D:\Program Files\VASCO\IAS-Backup\DB_BK\PRI_AUTH_DB_BAK_*.bak"') do del "D:\Program Files\VASCO\IAS-Backup\DB_BK\%%~A"

Open in new window

~bp
0
 
Skumar_CCSAAuthor Commented:
your both scripts worked excellent...
appropriated your kind and help...

small correction in the second script..

set BackupName=DB_BK\PRI_AUTH_DB_BAK_ (Not work)

set BackupName=PRI_AUTH_DB_BAK_  (Worked)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.