Having a number two for log backups

The server which holds the production log backups was not reponding yesterday. It was a pain to go into the tran log backup jobs and manually change them to a new share until the problem was resolved, so I decided to add a fallback step if the default maintainence job failed.

The sql below is heavily inspiried and the error handling at the end totally copied from Tara Kizer’s blog. The big insight was I didn’t know backup and restore commands worked with parameters for the database and destination. 

I didn’t add sql to get rid of old files as it is for when we’re problems and while hopefully that isn’t for too long I’d rather not have to be wondering if the log backups are being deleted.

create proc p_backup_logs (
 @dbname sysname = ‘all’
 , @path varchar ( 1000 )
 )
as

/*
 Make a step 2 for those convienent maintainence plans in case they fail so the
 log backups still occure.
 */

set nocount on

create table #databases ( name sysname )
create table #output ( output varchar ( 8000 ) )

declare @rc int
 , @sql varchar ( 8000 )
 , @name sysname — used in the cursor for holding sysdatabase.name
 , @filename varchar ( 255 )

— @path must have a at the end
if right ( @path , 1 ) <> ”
 set @path = @path + ”

— check if this sql server can access the passed path
set @sql = ‘dir “‘ + @path + ‘”‘
insert into #output
exec master..xp_cmdshell @sql

if exists ( select * from #output where output like ‘%Access is denied.%’ )
begin
 set @rc = 1
 goto exit_procedure
end

if exists ( select * from #output where output like ‘%The filename, directory name, or volume label syntax is incorrect.%’ )
begin
 set @rc = 2
 goto exit_procedure
end

— populate a table with online databases that are not in simple recovery
insert into #databases
select name
from master..sysdatabases
where 1 =
 case when @dbname = ‘all’ then 1
  when name = @dbname then 1
  else 0
 end
 and databasepropertyex ( name , ‘Recovery’ ) <> ‘simple’
 and databaseproperty ( name , ‘IsOffline’ ) = 0
 and databaseproperty ( name , ‘IsShutDown’ ) = 0
 and name not in ( ‘master’ , ‘model’ , ‘msdb’ , ‘resource’ )

— something is wrong if no databases fit the criteria so raise an error
if @@rowcount = 0
begin
 set @rc = 3
 goto exit_procedure
end

declare cur cursor for select name from #databases
open cur
fetch next from cur into @name

while @@fetch_status = 0
begin
 — create a subdirectory for each database
 set @sql = ‘mkdir ‘ + @path + @name
 exec master..xp_cmdshell @sql

 truncate table #output
 
 — make a name with yyyymmddhhmmss added on
 set @filename = @path + @name + ” + @name + ‘_’ + convert ( char ( 8 ) , getdate() , 112 ) + replace ( convert ( varchar ( 8 ) , getdate() , 108 ) , ‘:’ , ” ) + ‘.trn’
 — Brackets and quotename didn’t work… set @name = quotename ( @name )

 backup log @name to disk = @filename
 restore verifyonly from disk = @filename
 
 fetch next from cur into @name
end

deallocate cur

exit_procedure:

if @rc <> 0
begin
 DECLARE @rm varchar(500)
 DECLARE @error table (returnCode int PRIMARY KEY CLUSTERED, returnMessage varchar(500))

 INSERT INTO @error(returnCode, returnMessage)
 SELECT  1, ‘Access denied in passed path ‘ UNION ALL
 SELECT  2, ‘Bad folder specified in passed path’ UNION ALL
 SELECT  3, ‘No databases to backup’

 set @rm = ( select returnMessage FROM @error WHERE returnCode = @rc )

 RAISERROR(@rm, 16, 1)
end

drop table #databases
drop table #output

return

This entry was posted in Day to day. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *