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_PatchDatabase_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:
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description 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' ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date
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:
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 backup script:
DECLARE @Patch varchar(1000) SELECT @Patch = (SELECT 'PATCHFULLDATABASE_ ' + convert(varchar(500),GetDate(),112) + '.bkp') BACKUP DATABASE DATABASE TO DISK=@Patch with compression
With the same query from MSSQLTIPS , I could find these results, using it with no date range:
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:
SELECT TOP 100 s.database_name, m.physical_device_name, 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, s.backup_start_date, 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, s.server_name, s.recovery_model 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 GO
Is there something to restart the header of the database
( I’m out of ideas )
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.