Listing 2: The sp_restore_databases Procedure

CREATE proc sp_restore_databases @loc varchar(100) =null, @backupdir varchar(100)
as
declare @dbname   sysname
declare @dbshort  sysname
declare @line     varchar(100)
declare @reststmt    varchar(600)
declare @ntstmt   varchar(100) 
declare @datafilename sysname
declare @logfilename sysname
declare @i        int
declare @pos1     int
begin
  set nocount on
  create table #listfile 
  ( Logical_name sysname,
    phisc_name sysname,
    type char(1), 
    fg varchar(30) ,
    siz bigint,
    maxsize bigint)
  create table #userdbs (line varchar(100))

  set @ntstmt = 'xp_cmdshell ' + char(39) + 'dir ' + @backupdir+ char(39)
  insert into #userdbs exec (@ntstmt)
  declare cdb cursor for select line from #userdbs 
    where upper(line) like '%BAK%'

  open cdb
  fetch cdb into @line
  while @@fetch_status = 0 
  begin 
    set @pos1 = CharIndex('.bak' ,@line,1)
    set @i = @pos1
    while substring (@line,@i,1) <> ' ' and @i > 1
    begin 
      set @i = @i - 1       
    end 
    set @dbname = substring (@line,@i+1,@pos1+3-@i)
    set @dbshort = rtrim(ltrim(replace(@dbname,'.bak','')))

    truncate table #listfile
    insert into #listfile    exec ('RESTORE filelistonly from disk=' + '''' +                  
      @backupdir + '\' + @dbname  + '''')

    select @datafilename = Logical_name from #listfile where type = 'D'
    select @logfilename = Logical_name from #listfile where type = 'L'

    set @reststmt = 'restore Database ' + @dbshort + ' from ' +
      'Disk = ' + char(39) +@backupdir + '\' + 
      rtrim(ltrim(@dbname)) + char(39)

    if Isnull (@loc,'*') <> '*' 
    begin 
      set @reststmt = @reststmt + ' with move ' + char(39) + @datafilename + char(39) +
        ' to ' + char(39) + @loc + '\' + @datafilename + '.mdf' + char(39) + 
        ', move ' + char(39) + @logfilename + char(39) + ' to ' + char(39) + 
        @loc + '\'  + @logfilename + '.ldf' + char(39)
    end
    print @reststmt
    exec (@reststmt)
    fetch cdb into @line
  end 
  close cdb
  deallocate cdb
  drop table #userdbs
  drop table #listfile
end
go