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\Main_%MM%-%DD%-%YYYY%.mdb

Copy %Source% %Destination% /Y

===========================================
REIUSAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
in powershell
$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
0
Paul TomasiCommented:
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

Open in new window

The code in lines 6 through 8 will transform something like: ' 1:06:12.65' to: '01061265'.
0
Paul TomasiCommented:
It might be better to use Microsoft's naming convention which uses (1), (2), (3) etc... So, your filenames will look something like the following:

    Main_04-12-2012 (1).mdb
    Main_04-12-2012 (2).mdb
    Main_04-12-2012 (3).mdb
    Main_04-12-2012 (4).mdb
    etc...
@echo off
Set "Source=E:\Data\DB"
Set "Destination=E:\Data\DB\DB-Backup"

set /a count=0
:loop
  set /a count+=1
  set "DateStamp=%Date:~4,2%-%Date:~7,2%-%Date:~10,4% (%count%)"
if exist "%Destination%\Main_%DateStamp%.mdb" goto loop

Copy "%Source%\Main.mdb" "%Destination%\Main_%DateStamp%.mdb" /Y

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Paul TomasiCommented:
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.
@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

Open in new window

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.
0
Paul TomasiCommented:
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_%DateStamp%.mdb" /Y >nul
0
Paul TomasiCommented:
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...
@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

Open in new window

0
REIUSAAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.