How I take Backups

Backups -- The first thing I learned as a DBA and probably one of the most important items a DBA has to have in his repertoire.

I am currently working in a smallish shop. We are about half a terabyte total.  Previously I worked in a shop where the largest database was 10 Terabytes -- that is a different scenario requiring a different approach.

But for me, currently, I get to have a lot of 'luxuries'.. The first one I run a checkdb before I do anything.  After I tested things, I noticed that just because dbcc checkdb finding errors does not kill the step in a SQL Agent Job.

That section of the SQL Agent Job looks like this (And is step one):

dbcc checkdb with no_infomsgs, all_errormsgs
IF @@ERROR <> 0
BEGIN
 DECLARE @DB SYSNAME = DB_NAME();
 RAISERROR ('DBCC FAILED FOR %s', 23, 1, @DB) WITH LOG
END

So if anything besides a happy zero hops out of dbcc checkdb -- this raises an error which sends an email/page etc.

For the next step, I do struggle with space issues (even on my smallish environments), but I flip-flop the backups.  So the space is doubled for backups (compressed), but I am covered in the event of a backup overwriting and failing (power loss, cpu fries, Llama's attack).  I essentially used DATEPART(dw, GETDATE()) % 2 <-- this is a 0 or a 1.

Works like so (Step Two):

DECLARE @EvenOrOdd BIT, @EvenOrOddWeek BIT 
SELECT @EvenOrOddWeek = (DATEPART(ww, GETDATE()) %2)
SELECT @EvenOrOdd = DATEPART(dw, GETDATE()) % 2 ^ @EvenOrOddWeek;
DECLARE @BackupFile NVARCHAR(250);
DECLARE @DatabaseName SYSNAME = 'DatabaseName'
SELECT @BackupFile = N'G:\BACKUPS\' + @DatabaseName + '\' + @DatabaseName + 'Backup-' +
CAST(@EvenOrOdd as NVARCHAR) + '.bak';
DECLARE @BackupName NVARCHAR(250) = @DatabaseName + N'-Full Database Backup'

BACKUP DATABASE @DatabaseName TO  DISK = @BackupFile
WITH NOFORMAT, INIT,  NAME = @BackupName, SKIP, NOREWIND,
NOUNLOAD, COMPRESSION,  STATS = 10, CHECKSUM;
declare @backupSetId as int;
select @backupSetId = position from msdb..backupset where database_name= @DatabaseName
and backup_set_id=(select max(backup_set_id) from msdb..backupset where 
database_name= @DatabaseNAme)
if @backupSetId is null 
begin 
  raiserror(N'Verify failed. Backup information for database ''%s'' not found.', 
  16, 1, @DatabaseName)
end
RESTORE VERIFYONLY FROM  DISK = @BackupFile WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

So I always have a backup available (as I am overwriting the 'older' one with INIT).  Sunday, Tuesday, Thursday, and Saturday get 1's, Monday, Wednesday, Friday get 0's.  EvenOrOdd week flips that so that you get continuity on end of weeks.

You can achieve the same effect with Ola Hallangren's nice scripts -- regardless of whatever solution I use I want to test.  I use Ola on all non prod environments, but maintain this strategy for production.  I also thoroughly tested the solution (moved files around, opened the datafile and borked it up with a hex editor etc).  To me this is really simple (which matches the environment currently) and straight forward.  I want it that way come time to restore from a Llama attack.