20

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:

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 

enter image description here

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


EDIT2:

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.


EDIT 3:

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') 

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:

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 )

EDIT 4:

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

EDIT5:

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 Jan 22 '16 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... – Steve Mangiameli Jan 26 '16 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 '16 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. – Anti-weakpasswords Feb 8 '16 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 '16 at 14:03
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 '16 at 16:56
  • Hi @RafaelPiccinelli. Did this work for you? – Kefash Aug 10 '17 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 '17 at 14:46
  • @john I need a fix for this sooo badly. was your issue eventually resolved? – Kefash Aug 11 '17 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 '17 at 17:29

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.