Codezilla, have you find a solution to your restore script yet ? I'm also looking for a script to restore 10 databases that I can run whenever needed. The backup files name changes everyday (it has the date attach to the database name) but the location of the backup files and the databases name are not. What I am doing for now is:

restore database dbname

from disk = 'path:\dbname_yyyymmddhh.BAK'

with dbo_only, -- this ensure that only user that have access to this db can access the db

replace, --to replace the pre-existing db with the same name

move 'logicaldbname' to 'path:\physical_dbname.mdf',

move 'logicaldb_logfilename' to 'path:\physical_dbname_log.ldf'

go

I do this for all of my 10 databases that I need to restore. Yes, painful, but you got to do what you got to do.

However, what I would like to do or find a way to do :

1. Get all the backup files (*.BAK) from the path:\ put them into a temp table

2. loop through the temp table created above and set @dbvar for the databasename and set @bkfile for that database backup files

3. then restore the dbs by putting in the @dbvar and @bkfile variables.

4. drop the temp table from step 1.

This pose a few problems. How can I find out what the logical and the physical name for the datafile and the logfile.

I'll be so happy if some one can help with this. If I find the solution for this, I'll post it here.

TIA