June 29, 2017 at 7:58 am
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)
June 29, 2017 at 8:16 am
newdba2017 - Thursday, June 29, 2017 7:58 AMThe 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
June 29, 2017 at 8:26 am
Sue_H - Thursday, June 29, 2017 8:16 AMnewdba2017 - Thursday, June 29, 2017 7:58 AMThe 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 7zipAny 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.
June 30, 2017 at 12:17 pm
newdba2017 - Thursday, June 29, 2017 8:26 AMOk, 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.
I don't use FileZilla. If you can do what you need to from the command line then any of the options would likely work. You can find the command line arguments for FileZilla here:
Command-line arguments (Client)
Sue
June 30, 2017 at 5:20 pm
Straight combination of sql server agent job with 2 steps
step 1 - execute command file - issue the ftp command to get the file onto a local folder and unzip it.
step 2 - execute t-sql - perform required steps to restore database. This could be a variation of drop/restore or restore with override.
On this step include any maintenance required on the database including dropping/creating users.
No powershell required, neither is SSIS (over kill for this in my opinion)
If file names vary or if you have a set of files that need to be processed sequentially then you can also add a sqlcmd step to load filenames onto a processing table, and then have the restore script read from that table and issue required restore commands using dynamic sql
June 30, 2017 at 7:02 pm
You don't need to install FileZilla on the server to use http://FTP. Windows comes with an FTP client built-in. I've used it to automate downloading files from a server using a stored procedure. Here's a sanitized script that'll download the ImportData.txt file, but you can adapt it to download whatever files you want. If you have to download all files, there's the mget command. You can also download multiple individual files. Essentially, any standard FTP command is available.
SET @strPath = 'E:\Download';
SET @strDataFile = 'ImportData.txt';
SET @strCmd = 'echo ftp_user_name>' + @strPath + '\script.ftp&' +
'echo ftp_password>>' + @strPath + '\script.ftp&' +
'echo bin>>' + @strPath + '\script.ftp & ' +
'echo prompt n>>' + @strPath + '\script.ftp & ' +
'echo lcd ' + @strPath + '>>' + @strPath + '\script.ftp & ' +
'echo cd /Inbox >>' + @strPath + '\script.ftp & ' +
'echo get ' + @strDataFile + '>> ' + @strPath + '\script.ftp & ' +
'echo quit>>' + @strPath + '\script.ftp'
EXECUTE master..xp_cmdshell @strCmd;
--shell out to the ftp script just created
SET @strCmd = 'ftp.exe -v -s:' + @strPath + '\' + 'script.ftp some_ftp_server.com';
EXECUTE master..xp_cmdshell @strCmd;
From there, you can check to make sure the database you want to create doesn't already exist. If so, drop it. Then restore from the backup you just downloaded. Once you get the procedure done, it's simple to schedule it as a step in a database job.
I hope this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply