So, as they say you are only as good as your last recoverable backup. This means you need to be restoring your backups to make sure everything is good. I have had this script for quite a while and I honestly don't remember if it was copied from somewhere or not. I really just wanted to make sure I didn't forget about it and maybe help someone else at some point. Anyway, here is a script to grab the last full backup (.bak extension) and the apply all transaction logs to that in order (.trn files). I have this running on SQL 2014 CU3 currently with no problems. The only catch is that it temporarily enable xp_cmdshell so if that is against your policy this script won't work. Also, it puts all secondary data files into the same folder as the default data directory. So if you have a lot of filegroups that need to be on different drives you would have to modify this script some.
DECLARE @serverName VARCHAR(100)
DECLARE @databaseNameToRestore VARCHAR(100)
DECLARE @folderPath VARCHAR(100)
--BEST TO USE UNC PATHS UNLESS RUNNING FROM LOCAL SERVER
SET @folderPath = '\\ServerName\ShareName\DatabaseName\'
SET @databaseNameToRestore = 'DatabaseNameToRestore'
SET @serverName = @@serverName
DECLARE @DataDirectory AS VARCHAR(1000)
DECLARE @LogDirectory AS VARCHAR(1000)
SELECT
@DataDirectory = CAST(SERVERPROPERTY('instancedefaultdatapath') AS VARCHAR(1000)),
@LogDirectory = CAST(SERVERPROPERTY('instancedefaultlogpath') AS VARCHAR(1000))
DECLARE @resetCMDShell BIT
SET @resetCMDShell = 0
IF (SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
FROM sys.configurations WHERE name = 'xp_cmdshell') = 0
BEGIN
SET @resetCMDShell = 1
EXEC sp_configure 'show advanced option', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
END
CREATE TABLE #bakFile ( bakFileName VARCHAR(2000))
DECLARE @cmdShell VARCHAR(1000)
SET @cmdShell = 'dir /B /O-D "'+@folderPath+'*.bak"'
--assumes only 1 bak file in folder
--if more than 1 newest is used
INSERT INTO #bakFile
EXECUTE master.dbo.xp_cmdshell @cmdShell
DECLARE @bakFileName VARCHAR(200)
SELECT TOP 1 @bakFileName = [bakFileName]
FROM #bakFile
WHERE [bakFileName] IS NOT NULL
AND [bakFileName] != 'NULL'
DECLARE @temp AS table (LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0),
Fileid tinyint,
CreateLSN numeric(25,0),
DropLSN numeric(25, 0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlocSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent BIT,
TDEThumbprint SQL_VARIANT)
INSERT INTO @temp
EXEC ('RESTORE FILELISTONLY FROM DISK = '''+@folderPath+@bakFileName+'''')
DECLARE bakCursor CURSOR
FOR SELECT t.logicalName, t.fileID FROM @temp AS t
ORDER BY t.Fileid
DECLARE @logicalName VARCHAR(100)
DECLARE @fileID VARCHAR(100)
DECLARE @sql VARCHAR(1000)
SET @sql = 'restore database ' + @databaseNameToRestore + ' from disk = ''' + @folderPath + @bakFileName + ''' '
SET @sql = @sql + ' with file = 1, norecovery, replace, stats = 10, '
OPEN bakCursor
FETCH NEXT FROM bakCursor INTO @logicalName, @fileID
WHILE @@fetch_status = 0
BEGIN
SET @sql = @sql + ' move ''' + @logicalName +''' to ''' + CASE WHEN @fileID = 1 THEN @DataDirectory+@databaseNameToRestore+'.mdf'','
WHEN @fileID = 2 THEN @LogDirectory+@databaseNameToRestore+'.ldf'','
ELSE @DataDirectory+@databaseNameToRestore+'.idx''' END
FETCH NEXT FROM bakCursor INTO @logicalName, @fileID
END
IF RIGHT(@sql,1) = ','
SET @sql = LEFT(@sql,LEN(@sql)-1)
PRINT @sql
EXEC (@sql)
SET @sql = ''
CLOSE bakCursor
DEALLOCATE bakCursor
DROP TABLE #bakFile
--DATABASE RESTORED IN RECOVERY
--GET ALL TRAN LOGS SORTED BY TIME
--OLDEST FIRST
CREATE TABLE #trnFile ( trnFileName VARCHAR(2000))
SET @cmdShell = 'dir /B /OD "'+@folderPath+'*.trn"'
INSERT INTO #trnFile
EXECUTE master.dbo.xp_cmdshell @cmdShell
DECLARE trnCursor CURSOR
FOR SELECT trnFileName FROM #trnFile AS tf
WHERE [trnFileName] IS NOT NULL
AND [trnFileName] != 'NULL'
AND trnFileName > @bakFileName
DECLARE @trnFileName AS VARCHAR(1000)
DECLARE @trnFileCount AS INT
DECLARE @i AS INT
/*make sure all tran logs are newer than full backup*/
SELECT @trnFileCount = COUNT(*) FROM #trnFile AS tf
WHERE [trnFileName] IS NOT NULL
AND [trnFileName] != 'NULL'
AND trnFileName > @bakFileName
OPEN trnCursor
SET @i = 1
FETCH NEXT FROM trnCursor INTO @trnFileName
WHILE @@fetch_status = 0
BEGIN
SET @sql = 'restore log ' + @databaseNameToRestore + ' from disk = ''' + @folderPath+@trnFileName+''' '
SET @sql = @sql + 'with file = 1, nounload, stats = 10'
SET @sql = @sql + CASE WHEN @i < @trnFileCount THEN ', norecovery' ELSE '' END
PRINT @trnFileName
EXEC (@sql)
SET @i = @i + 1
FETCH NEXT FROM trnCursor INTO @trnFileName
END
CLOSE trnCursor
DEALLOCATE trnCursor
DROP TABLE #trnFile
IF @resetCMDShell = 1
BEGIN
EXEC sp_configure 'show advanced option', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
END