Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Script to backup database, need to make new copy instead of overwrite old copy

Posted on 2012-04-10
7
Medium Priority
?
244 Views
Last Modified: 2012-04-13
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

===========================================
0
Comment
Question by:REIUSA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 
LVL 83

Expert Comment

by:David Johnson, CD, MVP
ID: 37830090
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 37835501
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
 
LVL 11

Accepted Solution

by:
paultomasi earned 2000 total points
ID: 37835580
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:paultomasi
ID: 37836491
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 37836748
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
 
LVL 11

Expert Comment

by:paultomasi
ID: 37836813
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
 

Author Closing Comment

by:REIUSA
ID: 37842414
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Assume you have an outside contractor who comes in seasonally or once a week to do some work in your office, but you only want to give him access to the programs and files he needs and keep all other documents and programs private. Can you do this o…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
This Micro Tutorial will give you basic overview of the control panel section on Windows 7. It will depth in Network and Internet, Hardware and Sound, etc. This will be demonstrated using Windows 7 operating system.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question