The situation I have is a bit complicated but I am sure for all the experts here, it's not. One of our vendor puts a backup file in our FTP folder (since we don't host that DB), I unzip the file and then restore the DB. My manager wants me to schedule it on a daily basis. So vendor is going to put the full + all diff backups in our ftp folder and I have to unzip backup files, then restore it everyday. Is there a way to automate this task? (I don't know PowerShell if that's what members are going to suggest)
Powershell would be an option but if you don't want to use that then SSIS would be another option.
If you wanted to do this in just t-sql, you would need to shell out with xp_cmdshell to unzip the files. You can find an example using 7zip here:
Unzip files from Stored Procedure using 7zip
Any of those approaches could be automated with a job. There are probably other alternatives as well. Some of it depends on what you are comfortable doing and if you have any limitations at your company with extended stored procedures.
Sue
Ok, thanks for the quick response. I am sorry, they put the files on their ftp folder and I have the credentials to access the folder. I am also not sure if it matter or not but I use FileZilla to unzip files since its a secure ftp.