Backup to individual folders

Brian Donahue

Here is another script that you can use in a SQL Server job to backup databases using SQL Backup. This script uses COM automation to create a separate folder for each database to keep things better-organized.

This can easily be modified to backup logs on a schedule by changing the @backupstring variable.

set nocount on 
/*
 This script does a full backup of all databases exc tempdb.
Backups are saved in a folder with the same name as the database
under the @backuppath. If the folder doesn't exist, it is created.
Backups are erased after 7 days and email is sent on fail to support.
To change this, modify @backupstring to change SQL Backup's params.
*/
declare @datestamp varchar(30) 
declare @databasename varchar(200) 
declare @backuppath varchar(500) 
declare @filename varchar(500) 
declare @backupstring varchar(1000) 
declare @failcount int 
declare @failflag int 
declare @hresult int /* store OLE automation result */
declare @ole_FileSys int /* filesystemobject */
declare @ole_Folder int /* folder object */
declare @ole_Source varchar (255)
declare @ole_Description varchar (255)
declare @foldername varchar (255)

create table #tmpsqberrors (failcode int) 

set @backuppath = 'd:\sql\mssql\sqlbackup\' 

EXEC @hresult=sp_OACreate 'Scripting.FileSystemObject', @ole_FileSys OUT
IF @hresult <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @ole_FileSys
    RETURN
END

declare dbs cursor for 
select NAME from sysdatabases 
where name != 'tempdb' 
order by dbid 

open dbs 
fetch dbs into @databasename 
while @@fetch_status != -1 begin 

set @datestamp = left(replace(replace(replace(convert(varchar(30), getdate(), 120), '-', ''), ' ', ''), ':', ''), 12) 
set @foldername=@backuppath + @databasename
set @filename = @backuppath + @databasename + '\' + @databasename + '_db_(full)_' + @datestamp + '.sqb' 

/* Check that the folder exists. If not, create it */
EXEC @hresult=sp_OAMethod @ole_FileSys, 'FolderExists', @ole_Folder OUT, @foldername
IF @ole_Folder=0
	BEGIN
		EXEC @hresult=sp_OAMethod @ole_FileSys, 'CreateFolder', @ole_Folder OUT, @foldername 
	END
IF @hresult <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @ole_FileSys, @ole_Source OUT, @ole_Description OUT
  SELECT @ole_Description='Failed to create backup folder: '+@ole_Description
	RAISERROR (@ole_Description, 16, 1)  
   GOTO CLEANUP
END
/* Ready to do the backup!!! */
set @backupstring = '-SQL "BACKUP DATABASE [' + @databasename + '] TO DISK = ''' + @filename + ''' WITH NAME = ''Database (' + @databasename + ') Full'', MAILTO_ONERROR=''support@mydomain.com'', ERASEFILES = 7, COMPRESSION = 2" -E' 

exec @failflag=master..sqlbackup @backupstring 
if @failflag=0
	BEGIN
	insert into #tmpsqberrors (failcode) VALUES (1)
	END
fetch dbs into @databasename 

END

CLEANUP:

close dbs 
deallocate dbs 

select @failflag=COUNT(*) FROM #tmpsqberrors
if @failflag > 0 begin 
RAISERROR ('Backup failed to complete successfully for all databases.', 16, 1) 
end 
EXEC sp_OADestroy @ole_FileSys
DROP TABLE #tmpsqberrors
set nocount off