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