Schedule SQL RESTORE (not backup)
I'm not familiar at all with sql scripts so correct me if I'm wrong here..
Won't the script that is generated when I run through sql backup wizard only backup the currently selected file?
For example... I'm trying to set up a solution where SQL backup backs up a copy of a database on a remote domain (our production servers) and then copies it to the local domain and then restores it to a copy we have on the local dev machine.. I want it to run every morning before work so that the dev team has a clean and updated DB to work with..
SQL backup is scheduled to do a backup.. and I have a tool that is copying the created file to our network..
The problem comes when I run through the restore wizard.. I have to select the browse for backup files to restore.. Obviously - because the dev database is not being backed up so sql backup has no record of that (so can't use the select from backup history option).. nor would that help in any case since it would be outdated info. So I add the most recent full backup and click through the wizard..
My question is won't the scheduled job I create with the sql wizard always select that same file?? Hence 1 week from now it will be a week behind because it's just restoring the same file over and over again which does no good..
Are there workarounds for this?? or am I just approaching in the wrong direction?
Thanks for any input.