Automated Restore Database from Last Full Backup

In my environment, I backup to many files (for faster backups), and need to restore to a custom location. This query gets latest full backup info and restores to the path you specify. Tested on SQL 2005/2008.



DECLARE @BackupFiles VARCHAR(500), @data_file_path VARCHAR(512), @log_file_path VARCHAR(512),
@RestoreFileList VARCHAR(2000), @RestoreStatement VARCHAR(3000), @MoveFiles VARCHAR(2000), @DBName VARCHAR(150)

DECLARE @filelist TABLE (LogicalName NVARCHAR(128) NOT NULL, PhysicalName NVARCHAR(260) NOT NULL, [Type] CHAR(1) NOT NULL, FileGroupName NVARCHAR(120) NULL, Size NUMERIC(20, 0) NOT NULL, MaxSize NUMERIC(20, 0) NOT NULL, FileID BIGINT NULL, CreateLSN NUMERIC(25,0) NULL, DropLSN NUMERIC(25,0) NULL, UniqueID UNIQUEIDENTIFIER NULL, ReadOnlyLSN NUMERIC(25,0) NULL , ReadWriteLSN NUMERIC(25,0) NULL, BackupSizeInBytes BIGINT NULL, SourceBlockSize INT NULL, FileGroupID INT NULL, LogGroupGUID UNIQUEIDENTIFIER NULL, DfferentialBaseLSN NUMERIC(25,0)NULL, DifferentialBaseGUID UNIQUEIDENTIFIER NULL, IsReadOnly BIT NULL, IsPresent BIT NULL, TDEThumbprint VARBINARY(32) NULL)

SET @data_file_path = 'E:\SQLData\'
SET @log_file_path = 'E:\SQLLog\'
SET @DBName = 'AuctionMain'

--Get last full backup:
SELECT @BackupFiles=COALESCE(@BackupFiles + ',', '') + 'DISK = N'''+physical_device_name+''''
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE backup_set_id = ( SELECT MAX(backup_set_id)
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.database_name = @DBName AND TYPE = 'D')

SELECT @RestoreFileList= 'RESTORE FILELISTONLY FROM ' + @BackupFiles + ' WITH FILE = 1 '

IF (@@microsoftversion / 0x1000000) & 0xff >= 10 --TDE capability
BEGIN
INSERT INTO @filelist (LogicalName,PhysicalName,TYPE,FileGroupName,Size,MaxSize,FileID,CreateLSN,DropLSN,UniqueID,ReadOnlyLSN,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,FileGroupID,LogGroupGUID,DfferentialBaseLSN,DifferentialBaseGUID,IsReadOnly,IsPresent,TDEThumbprint)
EXEC (@RestoreFileList)
END
ELSE
BEGIN
INSERT INTO @filelist (LogicalName,PhysicalName,TYPE,FileGroupName,Size,MaxSize,FileID,CreateLSN,DropLSN,UniqueID,ReadOnlyLSN,ReadWriteLSN,BackupSizeInBytes,SourceBlockSize,FileGroupID,LogGroupGUID,DfferentialBaseLSN,DifferentialBaseGUID,IsReadOnly,IsPresent)
EXEC (@RestoreFileList)
END

--next version, do a count on filename, any >1 put in alternate data/log location.
SELECT @MoveFiles=COALESCE(@MoveFiles + ',' , '') + 'MOVE N''' + LogicalName + ''' to N''' +
CASE WHEN TYPE = 'D' THEN @data_file_path+RIGHT(physicalname, CHARINDEX('\',REVERSE(physicalname),1)-1)
WHEN TYPE = 'L' THEN @log_file_path+RIGHT(physicalname, CHARINDEX('\',REVERSE(physicalname),1)-1)
ELSE 'Full Text - code not complete'
END
+''''
FROM @filelist

SELECT @RestoreStatement='RESTORE DATABASE [' + @DBName +'] FROM ' + @BackupFiles + ' WITH FILE = 1, ' + @MoveFiles + ', NOUNLOAD, REPLACE, STATS = 20'

PRINT @RestoreStatement
EXEC (@RestoreStatement)