Dynamic Backup with configurable # of backup files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Dynamic Backup with configurable # of backup files

Author

Topic

Haywood
Posting Yak Master

221 Posts

Posted - 2007-12-07 : 12:17:46

This is an initial piece of work to build a backup command and configure the number of backup files dynamically. I've had a desire to do this for a while and finally sat down yesterday and worked out what you see below. This is initially just a POC to see what it will take for me to incorporate this desire into my current backup routines. However, it is functional for quick ad-hoc work and can easily be converted to an sp, so here you go. :)

I was also prompted onto this by a desire that a member has indicated he wants for his backups as well. However I did not solve his exact request - he wanted dynamic backup directories & files so that he could stripe them across different devices. If I understood correctly. My routines use one share to backup the database, I don't think it's wise to start spreading individual backup files across different shares/disks (unless it's absolutely neccessary) as it creates a fairly complex set of backup and restore op's that if not well documented, can be...difficult for people to use other than the original author.

Looking for comments and feedback, I'm not really a guru at building strings in this manner and if there's a better way, I'd like to know.

[CODE]
SET NOCOUNT ON
GO

SET CONCAT_NULL_YIELDS_NULL OFF
GO
/******************************************************************************
**
** Name: Dynamic_Backup_Devices.sql
**
** Description: Create backup command with X number of backup files.
**
** Author: G. Rayburn
**
** Date: 11/08/2007
**
** Depends on: SQLAgent having network access to UNC path.
**
*******************************************************************************
** Modification History
*******************************************************************************
**
** Initial Creation: 11/08/2007 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/
DECLARE @NumBakFiles int
, @DBName sysname
, @BackupStr_01 varchar(256)
, @BackupStr_02 varchar(256)
, @BackupStr_03 varchar(256)
, @BackupStr_04 varchar(256)
, @CurrLoopOp int
, @DynString_01 varchar(2048)
, @DynString_02 varchar(1024)

SET @NumBakFiles = 3

SET @DBName = 'Foo'

SET @BackupStr_01 = 'BACKUP DATABASE [' + @DBName +']' + char(10) + char(13) + ' TO '
SET @BackupStr_02 = 'DISK = ''\\SERVERFOO\SQLDUMP\' + @@SERVERNAME + '\Databases\' + @DBName + '\' + @DBName
SET @BackupStr_03 = '_' + CONVERT(varchar(8),getdate(),112) + '_1.BAK''' + char(10) + char(13)
SET @BackupStr_04 = 'WITH INIT'

SET @DynString_01 = @BackupStr_01 + @BackupStr_02 + @BackupStr_03

-- Uncomment if you don't like the CONCAT_NULL_YIELDS_NULL setting.
-- SET @DynString_02 = ''

SET @CurrLoopOp = 2

WHILE @CurrLoopOp <= @NumBakFiles
BEGIN
SET @DynString_02 = @DynString_02 + ' , ' + @BackupStr_02 + '_' + CONVERT(char(8),getdate(),112) + '_' + CONVERT(varchar(2), @CurrLoopOp) + '.BAK''' + char(10) + char(13)
SET @CurrLoopOp = @CurrLoopOp + 1
END

SET @DynString_01 = @DynString_01 + @DynString_02 + @BackupStr_04

PRINT @DynString_01

-- EXEC (@DynString_01)
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources