Solved

Access VBA script to unzip a SQL Server backup file to a selected folder

Posted on 2016-10-16
12
70 Views
Last Modified: 2016-10-17
I need a VBA script to unzip a SQL Server backup file from a "tar" zipped file
I will send parameters to the script containing:
  • The location of the zipped (tar) file
  • The name of the file
  • The name of the database file within the zip file. Please note that this zipped file contains several backed up databases, but I only want one of them.
  • The location of the folder to extract the file to.

The script will unzip the file, using 7-Zip to the selected location.

All suggestions gratefully received.
0
Comment
Question by:rick_danger
[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
  • 6
  • 5
12 Comments
 
LVL 40

Expert Comment

by:als315
ID: 41845789
You can use Shell command to execute any program. For 7zip you can use this sub:
Public Sub un7zip(ITarL As String, ITarF As String, ODir As String, F As String)
'ITarL - location of archive, ITarF - archive name, ODir - output folder, F - File name
Dim cmd As String, retval As Double
cmd = "c:\Program Files\7-Zip\7z.exe e " & Chr(34) & ITarL & ITarF & Chr(34) & " -o" & Chr(34) & ODir & Chr(34) & " " & Chr(34) & F & Chr(34)
retval = Shell(cmd, vbNormalFocus)
End Sub

Open in new window

Check and correct path to file 7z.exe in your system
Usage sample:
Call un7zip("d:\downloads\", "Doc1.tar", "d:\tmp\test 1\", "Test.accdb")
0
 

Author Comment

by:rick_danger
ID: 41845845
Thanks, but I have a few problems.

Line 4 is the cmd line. If I stop the code and view the string it is trying to execute I get this:
c:\Program Files\7-Zip\7z.exe e "C:\Dropbox\Apps\plesk-backup\zipfile.tar" -o"C:\CCL\MGH Backup" "C:\Dropbox\Apps\plesk-backup\zipfile.tar\databases\admin_MGH_2\"

This seems wrong to me. Can you help further please?
0
 
LVL 17

Expert Comment

by:John Tsioumpris
ID: 41845849
If your tar contains one file then the problem is the F...you have set it wrong...the F is the name you want the file to have on the output folder
If your tar has multiple files the keep only the part up to
ODir & Chr(34) 

Open in new window

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 40

Expert Comment

by:als315
ID: 41845856
Yes, F is file name in archive. I've changed sequence of last two parametrs. Try this:
Public Sub un7zip(ITarL As String, ITarF As String, F As String, ODir As String )
I don't see file name with extension in your result. Can you upload small sample (with real file names, but with dummy content)?
0
 

Author Comment

by:rick_danger
ID: 41845860
These are my parameters:
ITarL = "C:\Dropbox\Apps\plesk-backup\"
ITarF = "zipfile.tar"
ODir = "C:\CCL\MGH Backup"
F = "C:\Dropbox\Apps\plesk-backup\zipfile.tar\databases\admin_MGH_2\"
0
 
LVL 40

Expert Comment

by:als315
ID: 41845862
F = "C:\Dropbox\Apps\plesk-backup\zipfile.tar\databases\admin_MGH_2\"
In your question was:
The name of the database file within the zip file
F should be file name inside your archive. Is it also a folder?

Can you open your archive in 7-zip GUI and show screenshot?
0
 

Author Comment

by:rick_danger
ID: 41845865
You are of course correct, but I thought it would need the path of the file, otherwise I wasn't sure how to go down to the correct level in the folder.
0
 
LVL 40

Expert Comment

by:als315
ID: 41845871
7-zip GUI should show correct file name. Do you have folders inside your archive?
0
 

Author Comment

by:rick_danger
ID: 41845877
Yes.
The folders are like this:
C:\Dropbox\Apps\plesk-backup\zipfile.tar\databases\

Then there are several folders beneath this level. The folder I am interested in is admin_MGH_2, which has the zip file: backup_dbdump_1610160701.zip

Sorry but I think I have mislead you a little. Can you still help?
0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 41846130
You should add "-r" to the end on unzip command:
Public Sub un7zip(ITarL As String, ITarF As String, F As String, ODir As String)
'ITarL - location of archive, ITarF - archive name, ODir - output folder, F - File name
Dim cmd As String, retval As Double
cmd = "c:\Program Files\7-Zip\7z.exe e " & Chr(34) & ITarL & ITarF & Chr(34) & " -o" & Chr(34) & ODir & Chr(34) & " " & Chr(34) & F & Chr(34) & " -r"
retval = Shell(cmd, vbNormalFocus)
End Sub

Open in new window

and use following parameters:
ITarL = "C:\Dropbox\Apps\plesk-backup\"
ITarF = "zipfile.tar"
F = "backup_dbdump_1610160701.zip"
ODir = "C:\CCL\MGH Backup"
0
 

Author Comment

by:rick_danger
ID: 41846322
Great - I have it working now.

Just one problem. After extracting the file, it asks
"Would you like to replace the existing file" whether it is there or not. So even the first time I run it, it asks the question.

I have the options of
"(Y)es / (N)o / (A)lways / (S)kip all / A(u)to rename all / (Q)uit?"

I answer A and it all works, but is there a way of suppressing this?
0
 

Author Closing Comment

by:rick_danger
ID: 41846430
I got around that last problem by using the "-y " parameter at the end of the string. According to the documentation, it answers "Yes" to all questions.

Thanks for this solution, it works really well!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

688 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