Batch script for database restoration

[dharol]

Total newbie here, many apologies if I have failed to find an extant answer to this question.
Using:
SQL Server 2000

I need to restore several hundred databases from an external drive to local drive.
Would like to batch, eg, call on a text file listing old and new db names/paths and loop through to end of text doc.

Currently have non-batching script:

DECLARE 
@olddir varchar(200), @olddb varchar(200),
@newdir varchar(200), @dbname varchar(200),
@dbdata varchar(200), @dblog varchar(200),
@oldfile varchar(200), @newfile varchar(200), @logfile varchar(200)
 
SET @olddir = 'E:\DATA\SQLdbBAKs\'
SET @olddb = 'mydb_xxxxx.BAK'
SET @newdir = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\'
SET @dbname = 'mydb'
SET @dbdata = @dbname + '_Data.mdf'
SET @dblog = @dbname + '_Log.ldf'
SET @oldfile = @olddir + @olddb
SET @newfile = @newdir + @dbdata + '_Data.mdf'
SET @logfile = @newdir + @dblog + '_Log.ldf'
 
RESTORE DATABASE @dbname
    FROM DISK = @oldfile WITH FILE = 1,
    MOVE @dbdata TO @newfile,
    MOVE @dblog TO @logfile
 
 

So
1) is there a way to loop through E:\DATA\SQLdbBAKs\, grab all the dbs and restore them locally
OR
2) if i had a text file:

olddb,newdb,olddir,newdir
mydb_xxxxx, mydb, E:\DATA\SQLdbBAKs\, C:\Program Files\Microsoft SQL Server\MSSQL\Data\
 

could i loop through the rows and assign column entries to my variables?

Thank you for help!
-d

[ck9663]

Yes.

1. Use xp_cmdshell to get the content of the folder. You can store the result into a table.
2. Loop through that table. Each row of that table represents a file (filter out the other text as result if dir *.* command).

Happy Coding!!!


~~ CK