Database Restores – Where’s my Transaction Log Backup?

February 15, 2011

Developers! DBAs! Has this ever happened to you? [caption id=“attachment_2222” align=“alignright” width=“200”] Surprise! It’s a database migration error![/caption] You’re chugging along on a Friday night getting ready for your weekend deployment. Your 2 liter of Shasta is ice cold, you have your all Rush mix tape, and you’re wearing tube socks with khakis. Things are looking up. You open up your deployment script. You’re confident because you’ve tested it in the QA environment and everything worked. You press F5 and lean back in your chair, confident that the script is going to fly through all of the changes. Suddenly, there’s an error and you’re choking in surprise on Shasta. In an ideal world, you could pull out your trusty log backups and do a point in time restore, right? What if you’ve never taken a transaction log backup? What if you only have full database backups? Can you still recover from this situation? The answer, thankfully, is yes. Let’s break something!``` USE ftgu; GO

– at midnight, we took our initial back up BACKUP DATABASE ftgu TO DISK = ‘C:\ftgu-1.bak’ GO

– customer data from the business is inserted – more customer data is inserted

– some kind of migration goes here

– insert a bad value INSERT INTO Bins (Shelf, Bin) VALUES (‘B’, 9) GO

SELECT GETDATE();

SELECT * FROM Bins WHERE Shelf = ‘B’ ORDER BY BinID DESC; GO

– wait for a bit WAITFOR DELAY ‘00:01:00’; GO

– do something dumb DELETE p FROM Products p JOIN Bins b ON p.BinID = b.BinID WHERE b.Shelf = ‘B’;

DELETE FROM Bins WHERE Shelf = ‘B’ GO

SELECT GETDATE(); GO We have a starting backup, no t-log backups, and we’ve gone and deleted some important data from the production database. How do we get it back? If we restored the database from our first backup we might lose a lot of data. Who knows when the last database backup was taken? Oh, midnight. So, in this case, we’d lose a day of data. Well, bugger. In a panic, we save the state of our broken database. – ack! BACKUP DATABASE ftgu TO DISK = ‘C:\ftgu-2.bak’; And then we realize that we also need our transaction log: – ah crap, I need to back up my log to get point in time recovery! BACKUP LOG ftgu TO DISK = ‘C:\ftgu-log-1.trn’; GO Here’s the kicker – the transaction log has never been backed up. (In my experience, this is all too common.) This database has been running for a week or a year or three years without any kind of transaction log backups. We’re screwed right? I mean, wouldn’t we have to apply all of the transactions from the log to the very first full backup we have? No. Let’s get started and restore our last good backup. We always have our backup with missing data, just in case we need it for some reason. – switch to master (need to make sure nobody else is using that database) USE master; GO

– restore the last full backup with known good data – make sure to specify NORECOVERY so we can – apply our transaction log backup RESTORE DATABASE ftgu FROM DISK = ‘C:\ftgu-1.bak’ WITH REPLACE, NORECOVERY; GO SQL Server is cunning and records the log sequence number (LSN) from the last full backup (technically it’s the start and end LSN from the last full backup). If we have a log backup that encompasses the relevant LSNs, we’re good to go. Since our transaction logs were never backed up before today, we’re safe. We’re going to use something called – restore the log backup until right before we started – this is called “point in time recovery” RESTORE LOG ftgu FROM DISK = ‘C:\ftgu-log-1.trn’ WITH STOPAT = ‘2011-02-13 10:03:55.653’;