I have a development server, and a production server. The dev server is inside our office at work inside our LAN.
The production server is a remotely hosted windows pc, that is connected to our web server, which is a seperate pc.
Now what I want is to have a batch file or some other tool to automatically download a recent backup, for which there are weekly backups done, and then restore that to the current copy of the database on dev server.
I know, I can create a scheduled task in windows to copy n zip a backup that the weekly backup takes, and I would need any suggestions or help for that, since the name is different because it is date/time stamp in the filename. I want to create a task to zip the backup and copy it to a folder that is accessible on the web server, they are both on the same network.
Then, I want the dev server to check for the existance of this latest backup zip file, and if exists, download it, unzip to a temp folder, then restore that database to the current one.
Since I may or may not be hear in the far future the more I can make this easy to use, and as automatic as possible, the better they will be.
Since there is no dba we can use to do this for this manaully.
Any suggestions?
I do not mind using software for this, but it has to be freeware, since there is no budget for this project.
Best Answer
Your solution is pretty much the way to go.
Personally I'd consider using SSIS to coordinate the tasks as well as sending emails on failure etc.