Sometimes, we only wish to restore to the end of a certain log backup or, if the trans-action log is unavailable because of a failure, then we may have no choice!
To set up this example, run Listing 5.2, which creates a FullRecovery database, operating in FULL recovery model, and performs a full database backup.
USE master
GO IF DB_ID('FullRecovery') IS NOT NULL DROP DATABASE FullRecovery;
GO
-- Clear backup history
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'FullRecovery' GO
CREATE DATABASE FullRecovery ON (NAME = FullRecovery_dat,
FILENAME = 'D:\SQLData\FullRecovery.mdf' ) LOG ON
( NAME = FullRecovery_log,
FILENAME = 'D:\SQLData\FullRecovery.ldf' );
ALTER DATABASE FullRecovery SET RECOVERY FULL GO
BACKUP DATABASE FullRecovery TO DISK = 'D:\SQLBackups\FullRecovery.bak' WITH INIT
GO
Listing 5.3: Create the FullRecovery database, operating in FULL recovery model.
In Listing 5.4, we create the SomeTable table, insert some data, and then take a log backup.
USE FullRecovery
GO IF OBJECT_ID('dbo.SomeTable', 'U') IS NOT NULL DROP TABLE dbo.SomeTable
GO CREATE TABLE SomeTable (
SomeInt INT IDENTITY , SomeCol VARCHAR(5) );
INSERT INTO SomeTable ( SomeCol )
SELECT TOP ( 10 )
REPLICATE('a', 5) FROM sys.columns AS c;
BACKUP LOG FullRecovery TO DISK = 'D:\SQLBackups\FullRecovery_log.trn' WITH INIT
GO
Listing 5.4: Create and populate SomeTable, in the FullRecovery database, and run a log backup.
Finally, in Listing 5.5, we update a row in SomeTable, make a note of the time immedi-ately afterwards (we'll need this for a later restore), and then perform a named, marked transaction (again, more on this shortly) which accidentally empties SomeTable.
/*UPDATE a row in the SomeTable*/
UPDATE dbo.SomeTable SET SomeCol = 'bbbbb' WHERE SomeInt = 1 SELECT GETDATE();
/*Note this date down, as we'll need it later 2012-10-05 16:23:06.740*/
/*A named, marked transaction with a missing WHERE clause*/
BEGIN TRANSACTION Delete_SomeTable WITH MARK DELETE dbo.SomeTable;
COMMIT TRANSACTION Delete_SomeTable
Listing 5.5: Update a row in SomeTable then delete the table contents in a marked transaction.
For this example, we're simply going to restore over the top of the existing FullRecovery database, to return it to the state in which it existed at the time of the log backup. Listing 5.6 restores our full database backup over the top of the existing database and then applies the log backup.
USE master
GO --restore the full database backup RESTORE DATABASE FullRecovery
FROM DISK='D:\SQLBackups\FullRecovery.bak' WITH NORECOVERY;
--restore the log backup RESTORE LOG FullRecovery
FROM DISK='D:\SQLBackups\FullRecovery_log.trn' WITH RECOVERY;
Listing 5.6: Restoring to the end of the log backup (no tail log backup).
In this case, however, we'll get a useful and descriptive error.
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "FullRecovery" has not been backed up. Use BACKUP LOG WITH NORECOVERY to back up the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3117, Level 16, State 1, Line 5
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE is terminating abnormally.
SQL Server is warning us that we're about to overwrite the transaction log of the existing FullRecovery database, and we have not done a tail log backup, so any operations in there would be lost forever. In this example, it means that after the proposed restore operation we'd lose forever the effects of Listing 5.5, which we haven't backed up. If we're certain that we don't need to perform that backup, we can override this check using WITH REPLACE when we restore the full backup, as follows, and then perform the log restore as normal.
RESTORE DATABASE FullRecovery
FROM DISK='D:\SQLBackups\FullRecovery.bak' WITH NORECOVERY, REPLACE;
Conversely, only use REPLACE when certain that a tail log backup is not required. In this example, we'll take that tail log backup, even though we don't need it right away. This pitches the database into a restoring state and we proceed immediately with the restore operation, as shown in Listing 5.7.
USE master
GO BACKUP LOG FullRecovery
TO DISK='D:\SQLBackups\FullRecovery_tail.trn' WITH INIT, NORECOVERY;
USE master
GO RESTORE DATABASE FullRecovery
FROM DISK='D:\SQLBackups\FullRecovery.bak' WITH NORECOVERY;
RESTORE DATABASE FullRecovery
FROM DISK='D:\SQLBackups\FullRecovery_log.trn' WITH RECOVERY;
USE FullRecovery
SELECT TOP 1 * FROM SomeTable
Listing 5.7: A tail log backup followed by a restore operation.
As you can see, we've returned the database to the state in which it existed after the first log backup. Of course, we could restore the database to the end of the tail log backup, as shown in Listing 5.8, but then we'd just be returning the database to the state where all data had been lost from SomeTable.
-- This time we don't want to back up the tail, so use REPLACE USE master
GO RESTORE DATABASE FullRecovery
FROM DISK='D:\SQLBackups\FullRecovery.bak' WITH NORECOVERY, REPLACE;
RESTORE LOG FullRecovery
FROM DISK='D:\SQLBackups\FullRecovery_log.trn' WITH NORECOVERY;
RESTORE LOG FullRecovery
FROM DISK='D:\SQLBackups\FullRecovery_tail.trn' WITH RECOVERY;
Listing 5.8: A full restore operation.
Query SomeTable and you'll see that there is no data. Let's now see how to get it back;
in order to do this, we'll need to stop our restore operation after the update in Listing 5.5, but before the rogue delete.