MS SQL differential backup strategies

Question

We recently had a failure on one of our servers. The server was not accessible and we could get any data from it. We had a backup plan that took a full backup every second day and then then a diff backup every 6 hour or something.

I use Jungle Disk to get the data from the server to a external storage and it was this that failed us this time. There will always be a delay between the diffrential backup is finnished and Jungle Disk has copied the file over to the cloud. And in this case our last diff backup was made like 1 hour before and therefore made all of our previous diff backups useless.

Is there any way to setup a diff backup so that i do not always have to have the latest version of the diff backup and just restore the backup with so many diff backups that i have accessible?

score 3 · Answer 1 · answered Sep 12 '12 at 19:13

Old thread, I know, but I ran across this while researching a different problem with JungleDisk.

The problem the OP had was each differential that was taken was the same name as the last, and JungleDisk overwrites the old cloud-based backup with the new file. Not a problem, unless the last backup to the cloud failed...which is what happened in his case.

But the answer to the OP's question is yes, in your maintenance plan, rename each differential backup with a date and time stamp. For example, here's plan that creates a filename like:

MyDatabaseName_Diff_2012-08-20T01-35-01.BAK

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 
DECLARE @fileNameNoExt VARCHAR(256) -- Used to name the backup from the NAME parameter
DECLARE @subDir VARCHAR(256) -- Used to create the subdirectory for the backup
DECLARE @backupSetId as int
DECLARE @noBackupErrorMessage VARCHAR(256)

SET @path = 'C:\Path\To\Your\Backups\'

SELECT @fileDate = REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),126), ':', '-'), '.', '')

-- Exclude the system databases, as well as any others you don't want to back up.
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '\' + @name + '_Diff_' + @fileDate + '.BAK'  
       SET @fileNameNoExt = @name + '\' + @name + '_Diff_' + @fileDate 
       SET @subDir = @path + @name

       EXECUTE master.dbo.xp_create_subdir @subdir

       BACKUP DATABASE @name TO DISK = @fileName WITH DIFFERENTIAL, NOFORMAT, NOINIT,  NAME = @fileNameNoExt, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

       -- Now verify the backup       
       SET @noBackupErrorMessage = N'Verify failed. Backup information for database ' + @name + ' not found.'
       select @backupSetId = position from msdb..backupset where database_name=@name and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@name )
       if @backupSetId is null begin raiserror(@noBackupErrorMessage, 16, 1) end
       RESTORE VERIFYONLY FROM  DISK = @fileName WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND


       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

BUT, if you're using JungleDisk, you may discover that the backup chain is broken, and you can't make differential backups!

score 2 · Answer 2 · answered Jul 14 '10 at 10:45

Protecting your SQL Server data starts with the discs - you put your data files on dedicated RAID drives (ideally RAID10), put your transaction log files on another RAID10 drive, and TEMPDB elsewhere. This is for performance reasons, but it's also for recoverability - if one of the drives fails, you've got a chance. RAID should allow rebuilds, but also if your data drive has failed, you should be able to get the latest transactions out of the transaction log.

Next comes SQL Server backups - they should go to a separate disc, and then get taken off the server either to tape, or another server off-site. Depending on the size of the databases, and your maintenance windows, a full daily backup might be appropriate - or a full backup weekly. On top of this, put frequent transaction log backups (hourly perhaps), and maybe differentials as well depending on the size of your databases.

The final part is checking. Frequently check your restores by actually doing the restore somewhere else. Check that the backups are going offsite somehow. Test test test

Thanks :) But i kind of know all this but as always it's a matter of money and so on. We can't spend to much money on a super SLA and this time we couldn't get access to the server for 24 h. My question was basically if it is possible to restore a database even if one of the diff backups where missing. — Olaj, Jul 14 '10 at 12:21
Yes, you just need the latest full backup - and a differential backup. So, if you take a full backup, and then three differentials across different days - you just restore the full backup, and any of the differentials depending on what you need. — Peter Schofield, Jul 14 '10 at 12:26

MS SQL differential backup strategies

2 Answers2