SQL Server – Using Transaction Logs Multiple Times

backuprestoresql servertransaction-log

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?

Best Answer

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.

Related Solutions

Sql-server – Backup Disappearing

Ok, so it turns out I didn't know that I couldn't roll back to an earlier version of my newly created database until a full backup was made. This is why I couldn't see any options to roll back to before midnight in the restore dialogue.

SQL Server 2008 R2 – Restore COPY_ONLY Full Backup with Transaction Logs

A full backup in SQL Server 2008 does not break the log chain. It only resets the differential base-lsn.

You also can restore log backups after restoring from a copy only. The following script demos that:

CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13 
TO DISK = 'BakTst13_Log_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13 
TO DISK = 'BakTst13_Log_2' WITH INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

It creates a database and a table and inserts 50 rows into that table. In between those inserts several backups are taken in this order:

Full
Log
Full
Full Copy_Only
Log

Next the database is dropped and restored like this:

1st Full
1st Log
2nd Log

The following SELECT demonstrates that the restore was successful.

This shows that neither a COP_ONLY nor a normal Full Backup break the log chain.

Then the database is dropped again and restored like this:

Copy_Only Full
2nd Log

Afterwards the SELECT demonstrates success again.

This demonstrates that you can use a COPY_ONLY full backup as the base of your Log Restore.

Differential tests

I created a DIFFERENTIAL version too:

CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_1' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_2' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_3' WITH DIFFERENTIAL,INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F1, D1, D2',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY; 
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore FC, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F2, D2, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_2' WITH NORECOVERY; 
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

This takes backups in this order:

1st Full
1st Differential
2nd Full
2nd Differential
Copy_Only Diff
3rd Differential

It then tries this restore route:

1st Full
1st Differential
2nd Differential

Step 3 fails with this error:

Msg 3136, Level 16, State 1, Line 4
This differential backup cannot be restored because the database has not been restored to the correct earlier state.

This shows that a normal full backup breaks the differential chain.

Next the database is dropped and this restore flow is attempted:

Copy_Only Full
3rd Differential

Step 2 fails with the same error as step 3 above. This shows that a copy only backup cannot be used as the base for a differential restore.

Then the database is dropped again and the following restore is executed:

2nd Full
2nd Differential
3rd Differential

The following select proves that this restore succeeded. This shows that a COPY_ONLY Full backup does not interrupt the differential chain.

Best Answer

Related Solutions

Sql-server – Backup Disappearing

SQL Server 2008 R2 – Restore COPY_ONLY Full Backup with Transaction Logs

Differential tests

Related Question