Generate restore commands for all SQL Server backups taken in the last day, while changing datafile locations.

create-restore-script.sql

DECLARE @source nvarchar(256),
        @destination nvarchar(256)

SET @source = 'X:\Websense SQL\MSSQL10_50.WEBSENSEWSG\MSSQL\DATA\'
SET @destination = 'C:\ClusterStorage\DBstore1\MSSQL12.WEBSENSEWSG\MSSQL\DATA\'

SELECT DISTINCT 'RESTORE DATABASE ' + database_name +
                ' FROM DISK = ''' + physical_device_name + ''''+
                ' WITH RECOVERY,' +
                ' MOVE ''' + datafile.logical_name + '''' +
                ' TO ''' + REPLACE(datafile.physical_name, @source, @destination) + ''',' +
                ' MOVE ''' + logfile.logical_name + '''' +
                ' TO ''' + REPLACE(logfile.physical_name, @source, @destination) + ''';'
  FROM msdb.dbo.backupset b, msdb.dbo.backupmediafamily m,
       (select * from msdb.dbo.backupfile where file_type = 'D') datafile,
       (select * from msdb.dbo.backupfile where file_type = 'L') logfile
  WHERE b.backup_finish_date > DATEADD(dd, -1, GETDATE())
    AND b.media_set_id = m.media_set_id
    AND b.backup_set_id = datafile.backup_set_id
    AND b.backup_set_id = logfile.backup_set_id