Script to Generate Restore Statements

http://sqldavel.blogspot.com/2013/03/script-to-generate-restore-statements.html


-- 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