Script to Generate Restore Statements

There are many instances when using the SSMS GUI is not practical.  One of these is when backing up or restoring large numbers of databases such as when doing server migrations and/or consolidations.  I wrote a simple script years ago that only read from the sys.databases table but that proved unusable the first time I ran into a database that had multiple data files.  The script below joins sys.databases with sys.master_files and the resulting script will generate the proper restore command no matter how many mdf, ndf or ldf files are in the database.  You will likely have to modify the code to adjust for your backup location or for different preferences regarding command line arguments.  This will run under SQL 2005 or SQL 2008.

-- Purpose:       Generate 'restore database' statements
-- Instructions:  Edit 'where' clause and/or backup path information in variables as
--         needed. Run script to generate sql, then edit/modify as needed and run selected sql statements.
--
set nocount ON

DECLARE @RestoreDB VARCHAR (8000)
Declare @Move varchar (8000)
Declare @MoveWithoutComma varchar (8000)
Declare @Go varchar (8000)
Declare @firsttime varchar (5) -- = 'True'
declare @sd_name varchar(255)
declare @smf_name varchar(255)
declare @smf_physical_name varchar (255)
declare @hold_sd_name varchar (255)

select @firsttime = 'True'

DECLARE Database_cursor CURSOR FOR
SELECT sd.name,smf.name, smf.physical_name
FROM sys.master_files AS smf inner join sys.databases AS sd
 on smf.database_id = sd.database_id

-- Edit where clause as desired
where sd.name not in ('master', 'tempdb', 'msdb', 'model')
ORDER BY sd.name
OPEN Database_cursor
FETCH NEXT FROM Database_cursor
INTO @sd_name, @smf_name, @smf_physical_name

Select @hold_sd_name = @sd_name
WHILE @@FETCH_STATUS = 0
Begin
    if @firsttime = 'True'
    Begin
  -- Edit backup path as needed
  select @RestoreDB = 'RESTORE DATABASE [' + @sd_name + ']' + char(13) + char(10) + 'FROM DISK = ''J:\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\'
                                + @sd_name + '.bak''' + char(13) + char(10) + 'WITH MOVE ''' + @smf_name + ''' TO '''
    + @smf_physical_name + ''', replace ,' + 'stats = 10' + char(13) + char(10)
   print @RestoreDB
 
  select @Go = 'GO' + char(13) + char(10)

   FETCH NEXT FROM Database_cursor
  INTO @sd_name, @smf_name, @smf_physical_name

  if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
    select @hold_sd_name = @sd_name
  else select @firsttime = 'False'
 end
 else
 Begin
  -- Move with comma ','
  select @Move = 'MOVE ''' + @smf_name + ''' TO '''
    + @smf_physical_name + ''','
  -- Last 'Move' without comma ','
  select @MoveWithoutComma = 'MOVE ''' + @smf_name + ''' TO '''
    + @smf_physical_name + ''''
  FETCH NEXT FROM Database_cursor
  INTO @sd_name, @smf_name, @smf_physical_name
  if @hold_sd_name <> @sd_name or @@FETCH_STATUS <> 0
  Begin
   print @MoveWithoutComma
   print @Go
   select @hold_sd_name = @sd_name
   select @firsttime = 'True'
  end
  Else print @Move
 end
END
CLOSE Database_cursor
DEALLOCATE Database_cursor
GO