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.

Refresh processes

Usually there is some kind of process that the DBA is involved in to move data from prod and put it into test environments.  If there is data in tables that drives the environment, you may want to 'persist' that data.  In order to do that effectively, I think it is important and helpful to have a way to verify that table A and table B are identical.

Below is how I accomplish that currently:

ALTER PROCEDURE usp_CompareTables @table1 VARCHAR(100), @table2 VARCHAR(100)
AS
BEGIN
  -- return 1 if identical 0 if not
  DECLARE @sql NVARCHAR(MAX);
  DECLARE @count INT;
  DECLARE @parmDefinition NVARCHAR(200) = '@countOUT INT OUTPUT';
  set @sql = 'select @countOUT = count(*) from (
  (select * from ' + @table1 + '
  except
  select * from ' + @table2 + ' )
  union all
  (select * from ' + @table2 + '
  except
  select * from ' + @table1 +')) a'
 BEGIN TRY 
  -- try to see if the tables are identical
  exec sp_executesql @sql, @parmDefinition, @countOUT=@count OUTPUT
 END TRY
 BEGIN CATCH
  -- if we errored out then the datatypes don't match or the tables are not 
  -- alike.. Or maybe the DBA is comparing a table to a giraffe
  RETURN 0 
 END CATCH

 IF @count = 0
  -- if we have nothing in the list we are identical
  return 1
 ELSE
  -- if not we got a problem
  return 0
END 
GO

Past that you will need something to truncate and load.  If there are foreign keys, things get messy -- but I accomplish that through the following code:

IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[Constraints]') AND type in (N'U'))
BEGIN
 DROP TABLE Constraints
END
  create table Constraints
  (
 ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 PKTABLE_QUALIFIER VARCHAR(50),
 PKTABLE_OWNER   VARCHAR(50),
 PKTABLE_NAME   VARCHAR(50),
 PKCOLUMN_NAME    VARCHAR(50),
 FKTABLE_QUALIFIER VARCHAR(50),
 FKTABLE_OWNER     VARCHAR(50),
 FKTABLE_NAME      VARCHAR(50),
 FKCOLUMN_NAME     VARCHAR(50),
 KEY_SEQ           INT,
 UPDATE_RULE       INT,
 DELETE_RULE       INT,
 FK_NAME           VARCHAR(256),
 PK_NAME           VARCHAR(256),
 DEFERRABILITY     INT
   )
GO
declare @database varchar(50)
declare @table varchar(50)
declare @sql   varchar(max)

-- Really could just use a while loop.. but cursors work too.
declare fk_cursor cursor for 
select  SourceDatabase, SourceTable from PersistedItems

OPEN fk_cursor
FETCH NEXT FROM fk_cursor into @database, @table



WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @SQL = 'use ' + @database + '; INSERT INTO [Database].[dbo].[Constraints] exec sp_fkeys ''' + @table + ''';'
 exec (@sql)
 FETCH NEXT FROM fk_cursor into @database, @table
END

close fk_cursor
deallocate fk_cursor
GO

-- Drop Constraints
use Database
GO

declare @database varchar(50)
declare @fktablename varchar(50)
declare @fkname varchar(50)
declare @sql   varchar(max)

declare drop_cursor cursor for 
select  PKTABLE_QUALIFIER, FKTABLE_NAME, FK_NAME from Constraints

OPEN drop_cursor

FETCH NEXT FROM drop_cursor into @database, @fktablename, @fkname

WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @SQL = 'use ' + @database + '; ALTER TABLE ' + QUOTENAME(@fktablename) + ' DROP CONSTRAINT ' + QUOTENAME(@fkname) + ';' from Constraints; 
 exec (@sql)
 FETCH NEXT FROM drop_cursor into @database, @fktablename, @fkname
END

close drop_cursor
deallocate drop_cursor
GO
use Database
GO

IF object_id('usp_RestoreTable') IS NULL
    EXEC ('create procedure dbo.usp_RestoreTable as select 1');
GO

ALTER PROCEDURE usp_RestoreTable
@SourceTableName NVARCHAR(100),
@PersistedTableName NVARCHAR(100),
@Database NVARCHAR (100),
@isIdentity BIT
AS
BEGIN

 DECLARE @SQL nvarchar(MAX) = ''
 DECLARE @Columns nvarchar(MAX) = ''
 CREATE TABLE #Columns
 (
  ColID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  ColName varchar(100)
 )


 SELECT @SQL = 'INSERT INTO #Columns SELECT COLUMN_NAME FROM [' + @Database + '].INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = ''' + @SourceTableName + ''' ORDER BY ORDINAL_POSITION'

 EXEC (@SQL)
 SELECT @SQL = ''

 SELECT @Columns += '[' + ColName + '], ' FROM #Columns
 SELECT @Columns = LEFT(@Columns, len(@Columns) - 1)

 DROP TABLE #Columns

 SELECT @SQL = 'TRUNCATE TABLE ' + @Database + '..' + @SourceTableName
 EXEC (@SQL)
 SELECT @SQL = 'USE ' + @Database + '; '
 IF (1 = @isIdentity) SELECT @SQL = @SQL + 'SET IDENTITY_INSERT ' + @SourceTableName + ' ON; ';
 SELECT @SQL = @SQL + 'INSERT INTO [dbo].[' + @SourceTableName + '] (' + @Columns + ') '
       + 'Select ' + @Columns + ' FROM [Database].[dbo].[' + @PersistedTableName + ']; '
 IF (1 = @isIdentity) SELECT @SQL = @SQL + 'SET IDENTITY_INSERT ' + @SourceTableName + ' OFF; ';

 exec (@SQL)

END
GO

--Move the data and verify
USE Database
GO
DECLARE @SourceTableName NVARCHAR(100),
@PersistedTableName NVARCHAR(100),
@Database NVARCHAR (100),
@isIdentity BIT,
@SourceTableFullName NVARCHAR(100),
@Matches BIT

DECLARE tandl_cursor CURSOR FOR
select SourceDatabase, SourceTable, PersistedTable, isIdentity from PersistedItems
OPEN tandl_cursor

FETCH NEXT FROM tandl_cursor into @Database, @SourceTableName, @PersistedTableName, @isIdentity

WHILE (@@FETCH_STATUS = 0)
BEGIN
 exec usp_RestoreTable @SourceTableName,@PersistedTableName,@Database,@isIdentity
 SELECT @SourceTableFullName = @Database + '..' + @SourceTableName
     exec @Matches = usp_CompareTables @table1 = @SourceTableFullName, @table2 = @PersistedTableName
 update PersistedItems 
 SET IsPostPersisted = @Matches 
 where SourceDatabase = @Database and SourceTable = @SourceTableName and PersistedTable = @PersistedTableName 
 FETCH NEXT FROM tandl_cursor into @Database, @SourceTableName, @PersistedTableName, @isIdentity
END

close tandl_cursor
deallocate tandl_cursor
GO

-- Create the FKeys
use Database
GO

declare @database varchar(50)
declare @fktablename varchar(50)
declare @fkname varchar(256)
declare @fkcolumnname varchar(50)
declare @pktablename varchar(50)
declare @pkcolumnname varchar (256)
declare @sql   varchar(max)

declare create_cursor cursor for 
select  PKTABLE_QUALIFIER, FKTABLE_NAME, FK_NAME, FKCOLUMN_NAME, PKTABLE_NAME, PKCOLUMN_NAME from Constraints

OPEN create_cursor

FETCH NEXT FROM create_cursor into @database, @fktablename, @fkname, @fkcolumnname, @pktablename, @pkcolumnname


WHILE (@@FETCH_STATUS = 0)
BEGIN
 select @SQL = 'USE ' + @database + '; '
    + 'ALTER TABLE ' + QUOTENAME(@fktablename) 
       + ' ADD CONSTRAINT ' + QUOTENAME(@fkname) 
    + ' FOREIGN KEY (' + QUOTENAME(@fkcolumnname) 
    + ') REFERENCES ' 
    + QUOTENAME(@pktablename) +
     '(' + QUOTENAME(@pkcolumnname) 
  + ');' FROM Constraints;
 
 exec (@sql)
 FETCH NEXT FROM create_cursor into @database, @fktablename, @fkname, @fkcolumnname, @pktablename, @pkcolumnname
END

close create_cursor
deallocate create_cursor
GO

This way I can simply add items to a driver table, and have the code handle the rest.