• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

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

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
rick_danger
Asked:
rick_danger
  • 6
  • 5
1 Solution
 
als315Commented:
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
 
rick_dangerAuthor Commented:
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
 
John TsioumprisSoftware & Systems EngineerCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
als315Commented:
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
 
rick_dangerAuthor Commented:
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
 
als315Commented:
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
 
rick_dangerAuthor Commented:
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
 
als315Commented:
7-zip GUI should show correct file name. Do you have folders inside your archive?
0
 
rick_dangerAuthor Commented:
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
 
als315Commented:
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
 
rick_dangerAuthor Commented:
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
 
rick_dangerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now