0

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]..."

enter image description here

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: enter image description here

0

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
--> 4 - Free space by drive.
EXEC master..xp_fixeddrives
---------------------------------------

replication

---------------------------------------
--> 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 > 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.