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
