REIUSA
asked on
Script to backup database, need to make new copy instead of overwrite old copy
I have a script that can be ran to copy a access database to another backup folder. It works well and adds the date to the end of the file but I wanted to see if there was a easy way to edit the script so that I can copy the same DB into the folder more than one time a day. The way it is now if I run it again it will replace the original copy but I want to have two copies with the same date. The problem is we need to make sure the file is backed up before any major changes are made and we need multiple copies in case we have to get data from a old database.
So I need to end up with something like this if I run it multiple times the same day.
Main_4-10-2012_A.mdb
Main_4-10-2012_B.mdb
Main_4-10-2012_C.mdb
Something like that.
Script:
========================== ========== ======
Set CurrentDate=%Date%Tips
Set CurrentTime=%Time%
Set YYYY=%CurrentDate:~10,4%
Set MM=%CurrentDate:~4,2%
Set DD=%CurrentDate:~7,2%
Set Source=E:\Data\DB\Main.mdb
Set Destination=E:\Data\DB\DB- Backup\Mai n_%MM%-%DD %-%YYYY%.m db
Copy %Source% %Destination% /Y
========================== ========== =======
So I need to end up with something like this if I run it multiple times the same day.
Main_4-10-2012_A.mdb
Main_4-10-2012_B.mdb
Main_4-10-2012_C.mdb
Something like that.
Script:
==========================
Set CurrentDate=%Date%Tips
Set CurrentTime=%Time%
Set YYYY=%CurrentDate:~10,4%
Set MM=%CurrentDate:~4,2%
Set DD=%CurrentDate:~7,2%
Set Source=E:\Data\DB\Main.mdb
Set Destination=E:\Data\DB\DB-
Copy %Source% %Destination% /Y
==========================
Another option is to add a timestamp to the filename like this:
Set Source=E:\Data\DB
Set Destination=E:\Data\DB\DB-Backup
set DateStamp=%Date:~4,2%-%Date:~7,2%-%Date:~10,4%
Set TimeStamp=%Time: =0%
Set TimeStamp=%TimeStamp::=%
Set TimeStamp=%TimeStamp:.=%
Copy "%Source%\Main.mdb" "%Destination%\Main_%DateStamp%_%TimeStamp%.mdb" /Y
The code in lines 6 through 8 will transform something like: ' 1:06:12.65' to: '01061265'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you want to generate the following filenames:
Main_4-10-2012_A.mdb
Main_4-10-2012_B.mdb
Main_4-10-2012_C.mdb
Main_4-10-2012_D.mdb
etc...
Then the following code will generate '_A' through '_Z' extensions.
Main_4-10-2012_A.mdb
Main_4-10-2012_B.mdb
Main_4-10-2012_C.mdb
Main_4-10-2012_D.mdb
etc...
Then the following code will generate '_A' through '_Z' extensions.
@echo off
Set "Source=E:\Data\DB"
Set "Destination=E:\Data\DB\DB-Backup"
call :GetNewDateStamp
Copy "%Source%\Main.mdb" "%Destination%\Main_%DateStamp%.mdb" /Y
exit /b
rem ----------------------------------------------
rem GET NEW DATESTAMP
rem ----------------------------------------------
:GetNewDateStamp
if "%1"=="" (
call :GetNewDateStamp A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
) else (
:loop
set "DateStamp=%Date:~4,2%-%Date:~7,2%-%Date:~10,4%_%1"
shift
if exist "%Destination%\Main_%DateStamp%.mdb" goto loop
)
goto :eof
NOTE: You should not attempt to go past 'Z'. If there is a possibility this might happen then I can re-write the code to generate 'A', 'B'... 'Z' then, 'ZA', 'ZB'... 'ZZ' then, 'ZZA', 'ZZB'... etc.
BTW, You can prevent the COPY command displaying "1 file(s) copied." by adding '>nul' on to the end of the line, like this:
Copy "%Source%\Main.mdb" "%Destination%\Main_%DateS tamp%.mdb" /Y >nul
Copy "%Source%\Main.mdb" "%Destination%\Main_%DateS
Just for good measure, I have re-written the above code to generate the following filename sequence:
Main_4-10-2012_A.mdb
Main_4-10-2012_B.mdb
::
Main_4-10-2012_Z.mdb
Main_4-10-2012_ZA.mdb
Main_4-10-2012_ZB.mdb
::
Main_4-10-2012_ZZ.mdb
Main_4-10-2012_ZZA.mdb
Main_4-10-2012_ZZB.mdb
::
Main_4-10-2012_ZZZ.mdb
Main_4-10-2012_ZZZA.mdb
etc...
Main_4-10-2012_A.mdb
Main_4-10-2012_B.mdb
::
Main_4-10-2012_Z.mdb
Main_4-10-2012_ZA.mdb
Main_4-10-2012_ZB.mdb
::
Main_4-10-2012_ZZ.mdb
Main_4-10-2012_ZZA.mdb
Main_4-10-2012_ZZB.mdb
::
Main_4-10-2012_ZZZ.mdb
Main_4-10-2012_ZZZA.mdb
etc...
@echo off
Set "Source=E:\Data\DB"
Set "Destination=E:\Data\DB\DB-Backup"
set "DateStamp=%Date:~4,2%-%Date:~7,2%-%Date:~10,4%_A"
if exist "%Destination%\Main_%DateStamp%.mdb" call :GetNewDateStamp
Copy "%Source%\Main.mdb" "%Destination%\Main_%DateStamp%.mdb" /Y >nul
exit /b
rem ----------------------------------------------
rem GET NEW DATESTAMP
rem ----------------------------------------------
:GetNewDateStamp
for %%a in (A B C D E F G H I J K L M N O P Q R S T U V W X Y Z) do (
if not exist "%Destination%\Main_%DateStamp:~0,-1%%%a.mdb" (
set "DateStamp=%DateStamp:~0,-1%%%a"
goto :eof
) else (
if "%%a"=="Z" (
set "DateStamp=%DateStamp:~0,-1%ZA"
call :GetNewDateStamp
)
)
)
goto :eof
ASKER
Thanks a lot, these are all really good options. I think the most copies in one day would be something like 10, more like 2 or 3.
$source = E:\Data\DB\Main.mdb
$date = get-date -format MMM-dd-yy-hh-mm
$destination = "E:\DATA\DB\DB-Backup" + $date + ".mdb"
copy $source $destination