I'm just messing around with the SSMS graphic interface and studying the options of the "restore" task.

One thing that I noticed is when I click on "generate script", the first line of the query is:

 RESTORE DATABASE [MyDatabase] FROM  DISK = N'Server_Patch\Database_name_LOGSHIPPING.BKP' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5 ( and a lot of log backups for point in time )

Ok, no problem, but, i'm doing daily backups of this database. this Database_name_LOGSHIPPING.BKP was the name of the file that I made for log shipping a month ago.

Why when I try to use the SSMS graphic interface to restore a backup, it points to this backup file? I don't even have this file anymore.

With this query from MSSQLTIPS I can see all backups from this database:

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.name AS backupset_name, 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)   and  msdb..backupset.type ='D'

enter image description here

What can be wrong here? I'm not using COPY ONLY.


i'M making daily manual backups to test, and even this way, SQL Server selects that old backup that doesn't exists anymore. When running RESTORE HEADERONLY... it says ( obviously ) the file does not exists.


This the GUI print:

1 enter image description here Wow Wow Wow WAIT A MINUTE !

This database was a restore from another server ( same server, different instances ). Huuum... I think the problem is here.

Can you guys see on "server" in the second picture? it has 2 servers. I'm using the one with the names instance GDLIC2014.

the Script:

enter image description here

The backup script:

DECLARE @Patch varchar(1000)

SELECT @Patch = (SELECT 'PATCH\FULL\DATABASE_ ' + convert(varchar(500),GetDate(),112) + '.bkp') 


With the same query from MSSQLTIPS , I could find these results, using it with no date range:

enter image description here

The red square is the wrong backup from the older instance, the the Blue square is the last backup taken ( the GUI should be using it )


Well, With this query to list Backup History, I see that every log and full are correctly listed:

CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date


Is there something to restart the header of the database

( I'm out of ideas )

  • 3
    Can you try using the latest SSMS GUI - 2014 ? There are many fixes incorporated
    – Kin Shah
    Jan 22, 2016 at 17:20
  • 1
    Have you set up a trace and then run through the GUI options to capture how SSMS is getting the information? Might be quicker... Jan 26, 2016 at 15:30
  • 1
    @RafaelPiccinelli - I feel that something simple is being overlooked, but not sure what it is. Did you restore the msdb database from the original to the GDLIC2014 instance? Could something be using the wrong connection string or path? Certainly the SQL Server is not making up data for the backups and restores. (As an aside, it is interesting that you are restoring _2.mdf, _3.mdf, and _4.mdf, but not a non-suffixed .mdf or a _1.mdf.)
    – RLF
    Jan 26, 2016 at 15:37
  • Quit using the GUI to restore. Look at the backup disk or tape to determine what you actually have, and start from those. Feb 8, 2016 at 1:43
  • Hello @Anti-weakpasswords. No no. I don't use GUI. I query the log sequence, and I work with it. I was just curious about the GUI, but then I saw this problem. i try to be away from GUI as much as I can.
    – Racer SQL
    Feb 10, 2016 at 14:03

1 Answer 1


Chances are something in the backup history tables got out of sync and the UI is going to the last "consistent" full backup. If you're really interested in why it's doing what it's doing, start a profiler trace limited to your account, walk through the restore steps in the GUI, and review the commands captured in the trace that show what the UI is doing behind the scenes. This will get you the definitive answer you're looking for.

If you'd rather just move past this, you can clear your backup history via EXEC [msdb].[dbo].[sp_delete_backuphistory] @oldest_date=getdate() (depending on the last time you ran this, you may want to clear it out a month at a time) and then take a new full, etc. I would suspect this will reset the GUI to use the proper backups going forward.

Finally, another option is to run this script authored by Wayne Sheffield. It may provide some more information regarding any issues with the backup chain. I didn't come across this until after I posted this answer originally, but hopefully it helps someone else out in the future.

  • Hey @john Thank you for your answer.. I did a declare @data datetime set @data=getdate() EXEC msdb.dbo.sp_delete_backuphistory @oldest_date=@data And deleted the history. I will make some tests to see if it worked. Thank you.
    – Racer SQL
    May 18, 2016 at 16:56
  • Hi @RafaelPiccinelli. Did this work for you?
    – Kefash
    Aug 10, 2017 at 17:37
  • hey @Kefash. I THINK it didn't work. I can't remember right now. but every time I got an answer, I immediately upvote and select as an answer. I think it didn't solve my problem.
    – Racer SQL
    Aug 11, 2017 at 14:46
  • @john I need a fix for this sooo badly. was your issue eventually resolved?
    – Kefash
    Aug 11, 2017 at 16:44
  • @RafaelPiccinelli I need to fix this as when i need to restore to a point to say add a database to the HAG it can be annoying. I know this can be done without the GUI but the transaction logs are taken every 10 mins. What were your other approaches?
    – Kefash
    Aug 11, 2017 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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