Schedule SQL RESTORE (not backup)

cfidoe

I read the knowledge base article..
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.

cfidoe

I found a workaround for this... If anyone is interested..
I changed sql backup to not "auto" name the backup file... It now names it as today.sqb
once it's copied down to the local machine on a folder named "today" - the sql restore runs with the script I created to restore today.sqb in the folder "today".. Then a batch file creates a folder named as the date. the batch file then copies the today.sqb from the "today" folder into this new %date% folder. Then a script runs later to delete the old copy in the today folder. This way the sql restore script can use a static database name but still keep the database up to date with different backups.
This wouldn't really be a problem for anyone (doing the scripts and everything) if they didn't need to keep the backups for a few months back in time like we do..

petey

Here's a possible implementation:

- backup on production server, using COPYTO to copy to development machine e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE prodDB TO DISK = [<AUTO>] WITH COPYTO = [\\devServer\backups\prodDB_Full]" '

- restore using search patterns and use MOVETO e.g

EXEC master..sqlbackup '-sql "RESTORE DATABASE prodDB_dev FROM DISK = [g:\backups\prodDB_Full\*.sqb] WITH MOVETO = [g:\backups\prodDB_Full\restored]" '

This performs the restore using whatever files are present in the 'g:\backups\prodDB_Full' folder (or you can refine it further e.g. prodDB_FULL_*.sqb), and moves the file(s) to 'g:\backups\prodDB_Full\restored' once the restore has completed.