Here is my code for SQL 2008 (the RESTORE_FILELIST_ONLY gives different output based on SQL version).

PRINT 'Starting up...'

PRINT CONVERT(varchar(100),GETDATE(),121)

DECLARE @dest_dbname varchar(255)

DECLARE @source_path varchar(255)

DECLARE @dest_filepath varchar(255)

DECLARE @log_filepath varchar(255)

DECLARE @stage_db_data_dir varchar(255)

DECLARE @stage_db_log_dir varchar(255)

-- These should be the only variables that you change

SET @dest_dbname = '[RTS_STAGE]'

SET @source_path = '\\srv-sql2008\SQLBackups\SomeDB\'

SET @dest_filepath = '\\srv-devsql2k8\MostRecent\SomeDB.bak'

SET @log_filepath = '\\srv-devsql2k8\MostRecent\SomeDB_copy_log.txt'

SET @stage_db_data_dir = 'E:\MSSQLData\'

SET @stage_db_log_dir = 'G:\MSSQLTranLogs\'

DECLARE @source_mask varchar(255)

DECLARE @dir_cmd varchar(255)

DECLARE @copy_cmd varchar(255)

DECLARE @singleuser_sqlcmd nvarchar(255)

DECLARE @multiuser_sqlcmd nvarchar(255)

DECLARE @dbrestore_sqlcmd nvarchar(2000)

SET NOCOUNT ON

/* ********

This block pulls a DIR from the path specified above and

then generates and executes a COPY command based on the

most recent file. NOTE: does nothing if no matching file is found

***** */

PRINT 'searching for a file...'

PRINT CONVERT(varchar(100),GETDATE(),121)

SET @source_mask = @source_path +'*.bak'

SET @dir_cmd = 'dir "'+@source_mask+'" /o-d /b'

CREATE TABLE #T ([output] varchar(255))

--fill temp table with the dir listing

INSERT INTO #T

EXECUTE xp_cmdshell @dir_cmd

DELETE FROM #T WHERE [output] IS NULL--remove empty lines

IF EXISTS (SELECT * FROM #T WHERE [output] LIKE '%.bak')

BEGIN

PRINT 'file found, copying file...'

PRINT CONVERT(varchar(100),GETDATE(),121)

--build COPY command

SELECT TOP 1 @copy_cmd = 'copy /V /B /Y /Z "' + @source_path + [output] +'" "'+@dest_filepath +'" > "'+@log_filepath+'"'

FROM #T

ORDER BY [output] DESC

EXEC xp_cmdshell @copy_cmd--run COPY command

SET @singleuser_sqlcmd = 'ALTER DATABASE '+@dest_dbname+' SET single_user WITH ROLLBACK IMMEDIATE'

SET @dbrestore_sqlcmd = 'RESTORE DATABASE '+@dest_dbname+' FROM DISK = '''+@dest_filepath+''' WITH REPLACE '

SET @multiuser_sqlcmd = 'ALTER DATABASE '+@dest_dbname+' SET multi_user WITH ROLLBACK IMMEDIATE'

CREATE TABLE #FILELISTONLY (

[LogicalName] [varchar](255) NULL,[PhysicalName] [varchar](255) NULL,[Type] [varchar](50) NULL,[FileGroupName] [varchar](255) NULL,[Size] [varchar](50) NULL,[MaxSize] [varchar](50) NULL,

[FileId] [varchar](50) NULL,[CreateLSN] [varchar](50) NULL,[DropLSN] [varchar](50) NULL,[UniqueId] [varchar](50) NULL,[ReadOnlyLSN] [varchar](50) NULL,[ReadWriteLSN] [varchar](50) NULL,[BackupSizeInBytes] [varchar](50) NULL,

[SourceBlockSize] [varchar](50) NULL,[FileGroupId] [varchar](50) NULL,[LogGroupGUID] [varchar](50) NULL,[DifferentialBaseLSN] [varchar](50) NULL,

[DifferentialBaseGUID] [varchar](50) NULL,[IsReadOnly] [varchar](50) NULL,[IsPresent] [varchar](50) NULL,[TDEThumbprint] [varchar](50) NULL

)

insert into #FILELISTONLY

exec('Restore FILELISTONLY FROM DISK='''+@dest_filepath+'''')

WHILE EXISTS (SELECT * from #FILELISTONLY WHERE [Type] IN ('D','L'))

BEGIN

DECLARE @UIDvarchar(50)

SELECT TOP 1 @UID=UniqueID FROM #FILELISTONLY WHERE [Type] IN ('D','L') ORDER BY FileID,UniqueId

SELECT TOP 1 @dbrestore_sqlcmd=@dbrestore_sqlcmd+

CASE [Type]

WHEN 'D' THEN ' , MOVE '''+[LogicalName]+''' TO '''+@stage_db_data_dir+[LogicalName]+'.mdf'' '

WHEN 'L' THEN ' , MOVE '''+[LogicalName]+''' TO '''+@stage_db_log_dir+[LogicalName]+'.ldf'' '

END

FROM #FILELISTONLY WHERE UniqueId=@UID

DELETE FROM #FILELISTONLY WHERE UniqueId=@UID

END

drop table #FILELISTONLY

/* ********

This block puts the DB in single user mode (killing all open connections) then

does a restore and finally puts the DB back in multi-user mode.

***** */

PRINT 'putting DB in single-user mode...'

PRINT CONVERT(varchar(100),GETDATE(),121)

EXECUTE sp_executesql @statement = @singleuser_sqlcmd

PRINT 'restoring DB='+@dest_dbname+' from FILE='+@dest_filepath+'... ('+@dbrestore_sqlcmd+')'

PRINT CONVERT(varchar(100),GETDATE(),121)

EXECUTE sp_executesql @statement = @dbrestore_sqlcmd

PRINT 'putting DB back in multi-user mode...'

PRINT CONVERT(varchar(100),GETDATE(),121)

EXECUTE sp_executesql @statement = @multiuser_sqlcmd

PRINT 'finished.'

PRINT CONVERT(varchar(100),GETDATE(),121)

END

DROP TABLE #T