Here is a bit of code, adapted from Nigel Rivett’s original. You can use this to copy one database over another or restore the most recent database over itself.

--BEGIN TRAN

IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[s_RestoreLatestBackup]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) 
    DROP PROCEDURE [dbo].[s_RestoreLatestBackup]
GO

CREATE PROC s_RestoreLatestBackup
    @dbname VARCHAR(128) ,
    @destdbname VARCHAR(128) ,
    @localBackupPath VARCHAR(200) ,
    @localDBPath VARCHAR(200) ,
    @localLogPath VARCHAR(200) ,
    @recipients VARCHAR(128),
    @filenamesuffix VARCHAR(50)
AS /*
exec s_RestoreLatestBackup
 @dbname = 'hrmprot' ,
 @destdbname = 'HRMPROT2',
 @localBackupPath = 'K:\Backups\' ,
 @localDBPath = 'I:\Data\' ,
 @localLogPath = 'J:\Logs\' ,
 @recipients = 'myemailaddress',
 @filenamesuffix = '_2'
*/ 

    DECLARE @cmd VARCHAR(2000) ,
        @filename VARCHAR(128) ,
        @s VARCHAR(128) ,
        @i INT ,
        @d DATETIME ,
        @sql NVARCHAR(2000) ,
        @StartDate DATETIME
 
    SELECT  @StartDate = GETDATE()
 
 -- drop database
    IF EXISTS ( SELECT  *
                FROM    master..sysdatabases
                WHERE   name = @destdbname ) 
        BEGIN
            SELECT  @cmd = 'drop database ' + @destdbname
            EXEC (@cmd)
        END
 
 -- get latest backup filename
    SELECT  @cmd = 'dir /B ' + @localBackupPath + @dbname + '\*.*'
    PRINT @CMD  
    CREATE TABLE #a ( s VARCHAR(2000) )
    INSERT  #a
            EXEC master..xp_cmdshell @cmd

    DELETE  #a
    WHERE   s IS NULL
            OR s NOT LIKE '%_backup_%'
 
    SELECT  *
    FROM    #a
 
    SELECT  @filename = MAX(s)
    FROM    #a
 
 -- Get files in backup
    SELECT  @cmd = 'restore filelistonly  from disk = ''' + @localBackupPath
            + @dbname + '\' + @filename + ''''
    PRINT @cmd 
    CREATE TABLE #files
        (
          [LogicalName] NVARCHAR(128) ,
          [PhysicalName] NVARCHAR(260) ,
          [Type] CHAR(1) ,
          [FileGroupName] NVARCHAR(128) ,
          [Size] NUMERIC(20,0) ,
          [MaxSize] numeric(20,0) ,
          [FileId] BIGINT ,
          [CreateLSN] NUMERIC(25, 0) ,
          [DropLSN] NUMERIC(25, 0) ,
          [UniqueId] UNIQUEIDENTIFIER ,
          [ReadOnlyLSN] NUMERIC(25, 0) ,
          [ReadWriteLSN] NUMERIC(25, 0) ,
          [BackupSizeInBytes] BIGINT ,
          [SourceBlockSize] INT ,
          [FileGroupId] INT ,
          [LogGroupGUID] UNIQUEIDENTIFIER ,
          [DifferentialBaseLSN] NUMERIC(25, 0) ,
          [DifferentialBaseGUID] UNIQUEIDENTIFIER ,
          [IsReadOnly] BIT ,
          [IsPresent] BIT ,
          [TDEThumbprint] VARBINARY(32)
        )
    INSERT  #files
            EXEC ( @cmd
                )
 
    SELECT  *
    FROM    #files
 -- buld the restore command
    SELECT  @cmd = NULL ,
            @s = ''
 
    WHILE @s < ( SELECT MAX(LogicalName)
                 FROM   #files
               ) 
        BEGIN
            SELECT  @s = MIN(LogicalName)
            FROM    #files
            WHERE   LogicalName > @s
   
  
            SELECT  @cmd = COALESCE(@cmd + ', move ', '') + '''' + @s
                    + ''' to '''
                    + CASE WHEN #files.Type = 'D' THEN @localDBPath
                           ELSE @localLogPath
                      END 
                    + CASE WHEN #files.Type = 'D' THEN + LogicalName + @filenamesuffix + '.mdf' 
         ELSE LogicalName + @filenamesuffix + '.ldf'
       END
                    + ''''
            FROM    #files
            WHERE   LogicalName = @s
        END
    SELECT  @cmd = 'restore database ' + @destdbname + ' from disk = '''
            + @localBackupPath + @dbname + '\' + @filename + ''' with move '
            + @cmd   -- + ', standby = ''' + @localBackupPath + 'standby.fil'''

    PRINT @cmd 
   
 --restore the database
 select (@cmd)
 
 exec (@cmd)
 
 drop table #files
 drop table #a
 
 -- Check that the restore was successful
 select @sql = 'select @i = count(*) from ' + @dbname + '..sysobjects select @d = crdate from master..sysdatabases where name = ''' + @dbname + ''''
 exec sp_executesql @sql, N'@i int out, @d datetime out', @i out, @d out
 
 /*
 if @d > @StartDate and @i > 20
 begin
  if @recipients is not null
  begin
   select @cmd = 'restore ' + @filename + ' completed successfully - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
   exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
  end
 end
 else
 begin
  select @cmd = '********************* restore ' + @filename + ' failed - started ' + convert(varchar(8), @StartDate, 112) + ' ' + convert(varchar(8), @StartDate, 108)
  if @recipients is not null
  begin
   exec master..xp_sendmail @subject = @cmd, @recipients = @recipients, @message = @@servername
  end
  raiserror(@cmd, 16, -1) 
 end
 */
go

--COMMIT TRAN