The below stored procedure performs a RESTORE of the newest BAK file in the specified directory (@bkpDir). I recommend putting it into an Admin database (hey just name it Admin!) rather than in master.
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_Restore
-- AUTHOR : Tara Duggan
-- DATE : November 3, 2005
-- INPUTS : @dbName - name to use for the restored database
-- @bkpDir - full path to the directory where the BAK files exist
-- @newDataLocation - new location and file name for the MDF, can be NULL
-- @newLogLocation - new location and file name for the LDF, can be NULL
-- OUTPUTS : None
-- RETURN CODES : 0 - success
-- 1 - invalid path
-- 2 - no BAK files exist in @bkpDir
-- 3 - backup contains more than 2 D files which isn't supported by this sproc
-- 4 - restore error
-- DEPENDENCIES : None
-- DESCRIPTION : This stored procedure performs a restore of the newest BAK file in a specified directory.
-- EXAMPLES (optional) :
/*
DECLARE @rc tinyint
EXEC @rc = dbo.isp_Restore @dbName = 'Db1', @bkpDir = 'H:\MSSQL\BACKUP\Db1', @newDataLocation = 'F:\MSSQL\DATA\Db1_Data.MDF', @newLogLocation = 'G:\MSSQL\DATA\Db1_Log.LDF'
PRINT @rc
*/
----------------------------------------------------------------------------------------------------
CREATE PROC dbo.isp_Restore
(@dbName sysname, @bkpDir nvarchar(1000), @newDataLocation nvarchar(1000), @newLogLocation nvarchar(1000))
AS
SET NOCOUNT ON
DECLARE @cmd sysname, @bkpFile nvarchar(1000), @sql nvarchar(4000), @logicalDataName nvarchar(128), @logicalLogName nvarchar(128)
-- Add a backslash to the end of the path if one doesn't exist
IF REVERSE(SUBSTRING(@bkpDir, 1, 1)) <> '\'
SET @bkpDir = @bkpDir + '\'
-- Get files sorted by date
SET @cmd = 'dir ' + @bkpDir + '*.BAK /OD'
CREATE TABLE #Dir(DirInfo VARCHAR(7000)) -- Stores the dir results
CREATE TABLE #BackupFiles(BackupDate varchar(10), BackupFileName nvarchar(1000)) -- Stores only the data we want from the dir
INSERT INTO #Dir
EXEC master.dbo.xp_cmdshell @cmd
IF EXISTS (SELECT * FROM #Dir WHERE DirInfo = 'The system cannot find the path specified.')
RETURN 1
INSERT INTO #BackupFiles
SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #Dir
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'
IF @@ROWCOUNT = 0
RETURN 2
-- Get the newest file
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC
DROP TABLE #Dir, #BackupFiles
SET @cmd = @bkpDir + @bkpFile
-- Stores RESTORE FILELISTONLY result set
CREATE TABLE #RestoreFileListOnly
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
[Size] numeric(20,0),
[MaxSize] numeric(20,0)
)
INSERT INTO #RestoreFileListOnly
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')
IF @@ROWCOUNT <> 2
RETURN 3
SELECT @logicalDataName = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'
SELECT @logicalLogName = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'
DROP TABLE #RestoreFileListOnly
SET @sql = ''
SET @sql = @sql + 'RESTORE DATABASE ' + @dbName + CHAR(10)
SET @sql = @sql + 'FROM DISK = ''' + @cmd + '''' + CHAR(10)
SET @sql = @sql + 'WITH' + CHAR(10)
SET @sql = @sql + CHAR(9) + 'REPLACE'
IF @newDataLocation IS NOT NULL
BEGIN
SET @sql = @sql + ',' + CHAR(10)
SET @sql = @sql + CHAR(9) + 'MOVE ''' + @logicalDataName + ''' TO ''' + @newDataLocation + ''''
END
IF @newLogLocation IS NOT NULL
BEGIN
SET @sql = @sql + ',' + CHAR(10)
SET @sql = @sql + CHAR(9) + 'MOVE ''' + @logicalLogName + ''' TO ''' + @newLogLocation + ''''
END
PRINT @sql
EXEC(@sql)
IF @@ERROR <> 0
RETURN 4
ELSE
RETURN 0
GO
If you don't use BAK as the extension for your backups or perhaps you are restoring transaction logs instead, then just change the line directly below the comment “Get files sorted by date“ to whatever meets your requirements.