We have a database for each major version of our system, for each customer, for both development and test. Like so:
WHATEVER44_CUSTOMERA_DEV WHATEVER44_CUSTOMERA_TEST WHATEVER44_CUSTOMERB_DEV WHATEVER44_CUSTOMERB_TEST WHATEVER44_CUSTOMERC_DEV WHATEVER44_CUSTOMERC_TEST
Once in a while, we restore the latest backup of each of these to new databases with a higher version number:
WHATEVER45_CUSTOMERA_DEV WHATEVER45_CUSTOMERA_TEST WHATEVER45_CUSTOMERB_DEV WHATEVER45_CUSTOMERB_TEST WHATEVER45_CUSTOMERC_DEV WHATEVER45_CUSTOMERC_TEST
We do this in Management Studio by right-clicking on the existing database, Task > Restore. It automatically selects the latest backup set and all we need to do is change the name of the database to restore to.
We want to automate this with a script that given a list of existing database names creates these new versions of the databases, without user interaction (apart from running the script). Is it possible?