Point in time restore fails - backup file from before the restore point not available (why does this matter) I have tried to do a point-in-time restore of a database through the management studio gui, the point-in-time being yesterday morning (2016-04-12).

This fails with the error 


  "...cannot open backup device [name of backup from 2016-02-19]..."




so it cannot access the backup from two months ago - I know there's a very good reason for this, as that backup is no longer present (we don't keep backups that long).

My question is, why is SQL Server trying to access a backup file from two months ago for a restore to a point in time only yesterday? Is there something I'm doing wrong here?

I've checked all my settings in the restore wizard gui (to make sure I hadn't got the date wrong). All the required (Or the only ones I think should be required) backup files are correct and accessible. I am going to just restore the 2016-04-11 nightly backup manually anyway so the urgency for this is off, but it's still interesting/annoying. 

I can't see anything in BOL that explains why SQL server is trying to access an old backup for this.

Setup info:


SQL Server 2012 (SP3) 
FULL Recovery model 
Nightly full backups 
T-log backups every 10 minutes          


Restore DB screenshot:
for a restore to a point in time, you just need the latest full-backup and all the transaction logs thereafter.

this script will show you when was the last backup taken and where it is located.

declare @backup_type CHAR(1) = 'D' --'D' full, 'L' log



                ;with Radhe as (
                    SELECT  @@Servername as [Server_Name],
                    B.name as Database_Name, 
                    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
                    ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
                    ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
                    ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
                    ,media_set_id = MAX(A.media_set_id)
                    ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,A.type
                    FROM sys.databases B 

                    LEFT OUTER JOIN msdb.dbo.backupset A 
                                 ON A.database_name = B.name 
                                AND (@backup_type IS NULL OR A.type = @backup_type  )

                    GROUP BY B.Name, A.type

                )

                 SELECT r.[Server_Name]
                       ,r.Database_Name
                       ,[Backup Type] = r.type 
                       ,r.DaysSinceLastBackup
                       ,r.LastBackupDate
                       ,r.BackupSize_GB
                       ,r.BackupSize_MB
                       ,F.physical_device_name
                       ,r.[AVG Backup Duration]
                       ,r.[Longest Backup Duration]

                   FROM Radhe r

                    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                                 ON R.media_set_id = F.media_set_id

                    ORDER BY r.Server_Name, r.Database_Name


for the transaction logs, use the same script above with @backup_type CHAR(1) = 'L'

see what you have inside your backup file doing a restore filelistonly

RESTORE FILELISTONLY FROM DISK=N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak'
--- CHECK MDF ,NDF AND log files with size



  check the disk space available


---------------------------------------
--BEFORE RESTORING - ALWAYS CHECK THE AVAILABLE DISK SPACE
--&gt; 4 - Free space by drive.
EXEC master..xp_fixeddrives
---------------------------------------



  replication


---------------------------------------
--&gt; 5 - see if the database is involved in replication
---------------------------------------
SELECT 
    name as [Database name],
    CASE is_published 
        WHEN 0 THEN 'No' 
        ELSE 'Yes' 
        END AS [Is Published],
    CASE is_merge_published 
        WHEN 0 THEN 'No' 
        ELSE 'Yes' 
        END AS [Is Merge Published],
    CASE is_distributor 
        WHEN 0 THEN 'No' 
        ELSE 'Yes' 
        END AS [Is Distributor],
    CASE is_subscribed 
        WHEN 0 THEN 'No' 
        ELSE 'Yes' 
        END AS [Is Subscribed]
FROM sys.databases
WHERE database_id &gt; 4


this is an example of a point in time restore.
the database is called IntEng2 and it already existed, so it needs to be overwritten.

it will be restored to this point in time: 2014-08-22T11:30:00

--================================================
-- RESTORE THE DATABASE TO A POINT IN TIME - the database IntEng2 is NOT replicated
--================================================

-- put database in single user mode - only me - the man doing the job ;)
USE [master]
ALTER DATABASE [IntEng2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- backup the tail of the log
BACKUP LOG [IntEng2] TO  DISK = N'H:\SQLBackups\IntEng2_LogBackup_2014-08-22_14-48-05.bak' WITH NOFORMAT, NOINIT,  NAME = N'IntEng2_LogBackup_2014-08-22_14-48-05', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5


-- restore the full backup - with norecovery
-- with replace on the log file because for some reason this database has 2 log files and we want to keep it like that
RESTORE DATABASE [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak' 
WITH  FILE = 1,  MOVE N'IntEng2_Log2' TO N'G:\SQLLog\IntEng2_Log2.ldf',  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5


-- resotore the log files - IN THE SPECIFIC ORDER ---
-- with NORECOVERY

RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_030002_2577183.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_031501_5336409.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_033001_6524314.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_034501_7888108.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_040002_3541104.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_041506_9327770.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_043001_9532881.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_044502_0339177.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_050002_0942167.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_051506_3762732.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_053005_8234237.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_054505_2202118.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_060002_3445813.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_061502_1611989.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_063002_2204240.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_064501_8698190.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_070002_1582584.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_071501_8781090.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_073002_2857856.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_074502_4935206.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_080001_8744757.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_081501_8779343.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_083001_7488322.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_084501_5171402.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_090002_4606122.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_091501_9724206.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_093002_3221461.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_094502_0465413.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_100001_7103846.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_101501_8737945.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_103002_2375779.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_104501_8634658.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_110002_4519941.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_111501_5088114.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5


-- restore the log closest to the time when the disaster happened
-- this will be the restore with STOP AT
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_113001_5819154.trn' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5,  
STOPAT = N'2014-08-22T11:30:00'


--================================================
-- BRING THE DATABASE BACK TO LIFE
--================================================
-- have a look at the database,
-- if everything is ok
-- bring the database back to multiuser
ALTER DATABASE [IntEng2] SET MULTI_USER

GO


there were other things I did not include here:

1) before a restore - make sure all the processes that use that database are stopped.

2) before a restore - script all the permissions, so that you can re-apply them later on.