?
Solved

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

Posted on 2016-10-16
12
Medium Priority
?
78 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
  • 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 19

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 2000 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

Industry Leaders: 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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

830 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