2

Every night a full backup is made of all the user databases. Every 15 minutes, transaction logs are created. Today i succesfully restored a database with the use of transaction logs. I set the time (08:30) and set it with the option WITH REPLACE, and pressed the Ok button.

It looked like this.

enter image description here

Afterwards (a hour later) i wanted to restore it again. But i saw no full backup and transaction logs. (transaction logs are still created on the server)

enter image description here

Any suggestions for restoring databases with transaction logs for multiple times? Or is this not an option?

1 Answer 1

3

Full backup is not showing up because it has already been restored and no new latest info is there in msdb to reflect the same. Moreover, once msdb is updated with the backup info, it lets you avail with such info and in a manner of chain as followed in as LSN.

However, you can do this manually by running below script on a specific database to retrieve backup information for. The process will then find the latest Full backup and all transaction log backups that have occurred after this full backup.

DECLARE @databaseName sysname 
DECLARE @backupStartDate datetime 
DECLARE @backup_set_id_start INT 
DECLARE @backup_set_id_end INT 
DECLARE @max_family int

-- set database to be used 
SET @databaseName = 'enterDatabaseNameHere'  

SELECT @backup_set_id_start = MAX(backup_set_id)  
FROM  msdb.dbo.backupset  
WHERE database_name = @databaseName AND type = 'D' 

SELECT @backup_set_id_end = MIN(backup_set_id)  
FROM  msdb.dbo.backupset  
WHERE database_name = @databaseName AND type = 'D' 
AND backup_set_id > @backup_set_id_start 

select  @max_family=max(family_sequence_number) 
FROM    msdb.dbo.backupset b, 
    msdb.dbo.backupmediafamily mf 
WHERE   b.media_set_id = mf.media_set_id 
AND     b.database_name = @databaseName 
AND     b.backup_set_id = @backup_set_id_start 

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999 

SELECT  backup_set_id, family_sequence_number,
    case mf.family_sequence_number 
        when 1 then 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' 
        else '                         , DISK = '''
    end 
        + mf.physical_device_name + 
    case mf.family_sequence_number
        when @max_family then ''' WITH NORECOVERY' 
        else ''''
    end
FROM    msdb.dbo.backupset b, 
    msdb.dbo.backupmediafamily mf 
WHERE   b.media_set_id = mf.media_set_id 
AND     b.database_name = @databaseName 
AND     b.backup_set_id = @backup_set_id_start 
UNION 
SELECT  backup_set_id, 1, 
    'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' + mf.physical_device_name + ''' WITH NORECOVERY' 
FROM    msdb.dbo.backupset b, 
    msdb.dbo.backupmediafamily mf 
WHERE   b.media_set_id = mf.media_set_id 
AND     b.database_name = @databaseName 
AND     b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end 
AND     b.type = 'L' 
UNION 
SELECT  999999999 AS backup_set_id, 1, 
    'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY' 
ORDER BY backup_set_id, 2

Also, you can go through the link here for more option to get the same.

1
  • Thank you, i tested the script, and it works great! It's to bad this can't be done with the GUI. Apr 20, 2015 at 13:17

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.