I wasn’t really planning on writing anything on this topic, but then I thought about my readers…uh wait…I’m the only one reading this blog, LOL! Here we go!
A few management folks at my organization, you know the type, the ones above your pay grade. Thought it would be a wonderful idea to have one of our most important databases backed up to a laptop off site each night. So in the event of a disaster, (Hurricane) the laptop could be scooped up, taken to a hotel, and work could still be processed. So they come to me, and tell me to make it happen….ASAP! After all, hurricane season had just started.
So we will call the source server, “Point A” and the destination laptop “Point B”. I already knew this situation might get a bit complicated. The database on Point A is set to Full recovery mode, a full backup every night, two diffs during the day, followed by transaction logs every 15 minutes, nothing special there. The good news I only had to restore a full backup down to Point B, so its recovery model is simple. I knew once I got the backup to Point B, the rest would be easy. Because, of the way SQL Server backups work, I didn’t want to take an additional Full backup to copy over to Point B, making my backups out of sequence. It’s like being Marty McFly and altering time, we all remember how that turned out! I first thought I was going to have to write a powershell script that would copy my latest backup, and move it via UNC path to Point B. Then I ran across this little jewel. Enter Copy Only Backups, simple, and easy to prove they are working!
So I must admit, as a SQL Rook, I need a few more tools in my tool belt then the seasoned vets out there, and one little helper I really enjoy using is Ola Hallengren’s backup strategy, I consider it my MacGyver Knife at this stage in my career.
Here is a sample script that I am working with using Ola’s method with Copy Only:
EXECUTE dbo.DatabaseBackup <——Download the code here
@Databases = ‘AdventureWorks’,
@Directory = ‘\\LaptopName\ShareName’,
@BackupType = ‘Full’,
@Verify = ‘Y’,
@Checksum = ‘Y’,
That's it, run this script as a SQL Job within SSMS nightly and now my "Out of Sequence" backup isn't effecting my production backup sequence.
So at this point, now that I have my Full Backup on Point B, I just need to restore it nightly.
RESTORE DATABASE Adventureworks
FROM DISK = ‘C:\ShareName\AdventureWorks.bak’;
This too will be ran from a SQL job on Point B, nightly. Additionally, I do not exactly want to start collecting these out of sequence backups on Point B, so I will run a Cleanup SQL Job, nightly as well. And the final step would be to create a new Full backup sequence of the "Out of Sequence" backup...make sense? lol.
BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\Backups\AdventureWorks.BAK
Now, earlier in the post, I mentioned that it is easy to prove this out of sequence method. Refer back to this link...the jewel The Author uses LSN (log sequence number) information to track the backups, to compare and contrast the difference the LSN numbers when taking a normal backup and then taking a backup with Copy_Only (out of sequence).
So there you have it folks...Please leave your comments, better ideas are always well appreciated, from the SQL Vets out there. Cheerio!