Striped backups and cursor

Question

Striped backups and cursor

Richard Moore-400646

SSCrazy

Points: 2384
More actions
January 25, 2016 at 11:24 am

#310983

I'm attempting to merge two backup scripts together and can't quite seem to get my while loop in the correct place. The script should create striped backups for each of the databases in the cursor. Each year a new database is added to the cursor. The output for the 1st database is fine, it's when it goes to the next that the loop for the file count isn't correct. The script is below. Any help would be appreciated.
SET QUOTED_IDENTIFIER OFF
declare@command varchar(max),
@dbname varchar(30),
@StripeCnt varchar(3),
@backuppath varchar(100),
@prename varchar(17),
@fileext char(4),
@StripeCounter varchar(3)
SELECT @StripeCounter = @StripeCnt
SELECT @backuppath = '\\backuppath\'
SELECT @fileext = '.bak'
SELECT @StripeCounter = 4
SELECT @prename =
substring(convert(char(19), CURRENT_TIMESTAMP, 120),1,10)+ '-' +
substring(convert(char(19), CURRENT_TIMESTAMP, 120),12,2)+
substring(convert(char(19), CURRENT_TIMESTAMP, 120),15,2)+
substring(convert(char(19), CURRENT_TIMESTAMP, 120),18,2)
SET NOCOUNT ON
DECLARE databasecursor CURSOR FOR SELECT [name] FROM master.sys.databases with (nolock)
where [name] in ('db1','db2','db3')
OPEN databasecursor
FETCH NEXT FROM databasecursor INTO @dbname WHILE (@@fetch_status = 0)
BEGIN
SELECT @command = 'BACKUP DATABASE '+ quotename(@dbname) + ' TO DISK = '+ +@backuppath+@dbname+'_Full_Monthly_'+@prename+'_'+@StripeCounter+@fileext+''
SELECT @StripeCounter = @StripeCounter - 1
WHILE @StripeCounter > 0
BEGIN
SELECT @command = @command + CHAR(10) + ', DISK = ' + @backuppath+@dbname+'_Full_Monthly_'+@prename+'_'+@StripeCounter+@fileext +''
--print @command
SELECT @StripeCounter = @StripeCounter - 1
END
select @command = @command + ' with compression, stats, buffercount = 2200, maxtransfersize = 4194304'
print @command
--EXEC (@command)
FETCH NEXT FROM databasecursor INTO @dbname
END
go
deallocate databasecursor
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

You never reinitialize @StripeCounter, so after the first loop, it just stays at 0 and never gets set back to 4. In the cursor, before you set the value of @command to the backup database command, you'd need to reinitialize it to be whatever you want (incidentally, it looks like you set @StripeCounter to equal @StripeCnt, and then just set it to 4 without doing anything with it in between. That does nothing more nor less than just setting it to 4).

At any rate, you could also do this without the loop and cursor (and I'd prefer to get rid of them on general grounds), but the above is the reason for your current issue.

Cheers!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Would something like this work for you? I'm removing the loop and using a different method to concatenate as explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET NOCOUNT ON

declare@command varchar(max),

@dbname varchar(30),

@StripeCnt varchar(3),

@backuppath varchar(100),

@prename varchar(17),

@fileext char(4),

@StripeCounter varchar(3)

SELECT @StripeCounter = @StripeCnt

,@backuppath = '\\backuppath\'

,@fileext = '.bak'

,@StripeCounter = 4

,@prename = REPLACE( REPLACE( convert(char(19), CURRENT_TIMESTAMP, 120), ':', ''), ' ', '-')

DECLARE databasecursor CURSOR FOR

SELECT [name]

FROM master.sys.databases with (nolock)

--where [name] in ('db1','db2','db3')

OPEN databasecursor

FETCH NEXT FROM databasecursor INTO @dbname

WHILE (@@fetch_status = 0)

BEGIN

SELECT @command = 'BACKUP DATABASE ' + quotename(@dbname)

+ STUFF((SELECT CHAR(10) + ' , DISK = '+ +@backuppath+@dbname+'_Full_Monthly_'+@prename+'_'+StripeCounter+@fileext+''

FROM (VALUES('1'),('2'),('3'),('4'))x(StripeCounter)

FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 3, ' TO')

+ CHAR(10) + ' with compression, stats, buffercount = 2200, maxtransfersize = 4194304'

print @command

--EXEC (@command)

FETCH NEXT FROM databasecursor INTO @dbname

END

go

deallocate databasecursor

If you want to completely remove the loops, you can with something even shorter:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET NOCOUNT ON

declare@command varchar(max),

@dbname varchar(30),

@StripeCnt varchar(3),

@backuppath varchar(100),

@prename varchar(17),

@fileext char(4),

@StripeCounter varchar(3)

SELECT @StripeCounter = @StripeCnt

,@backuppath = '\\backuppath\'

,@fileext = '.bak'

,@StripeCounter = 4

,@prename = REPLACE( REPLACE( convert(char(19), CURRENT_TIMESTAMP, 120), ':', ''), ' ', '-')

SELECT @command = (SELECT 'BACKUP DATABASE ' + quotename([name])

+ STUFF((SELECT CHAR(10) + ' , DISK = '+ +@backuppath+[name]+'_Full_Monthly_'+@prename+'_'+StripeCounter+@fileext+''

FROM (VALUES('1'),('2'),('3'),('4'))x(StripeCounter)

FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 3, ' TO')

+ CHAR(10) + ' with compression, stats, buffercount = 2200, maxtransfersize = 4194304' + CHAR(10)

FROM master.sys.databases with (nolock)

FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)')

--where [name] in ('db1','db2','db3')

print @command

--EXEC (@command)

If you don't want any of these, you can simply add SET @StripeCounter = 4 after your inner loop.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Richard Moore-400646 SSCrazy Points: 2384 More actions · Answer 3

Thanks very much for the replies. I've corrected the counter issue and will look the versions that Luis provided.

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 4

Richard Moore-400646 (1/25/2016)
Thanks very much for the replies. I've corrected the counter issue and will look the versions that Luis provided.

Shifting gears a bit, I have two questions...

1. Have you actually compared the time it takes to make striped backups vs monolithic backups and

2. Have you guaranteed that each "stripe" is on separate physical spindles?

The reason I ask is because I've found that striping backups has actually slowed things down for me a bit (sometimes, substantially) unless I'm allowed to dedicate separate physical spindles to the process, all of which makes sense to me (but I tried it anyway).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Richard Moore-400646 SSCrazy Points: 2384 More actions · Answer 5

Hey Jeff,

Thanks for your reply. Funny you should ask that as I'm in the process of testing and verifying that information. Initial tests show marginal differences in times, but I haven't tested it on all available hardware as of yet.

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 6

Richard Moore-400646 (1/25/2016)
Hey Jeff,
Thanks for your reply. Funny you should ask that as I'm in the process of testing and verifying that information. Initial tests show marginal differences in times, but I haven't tested it on all available hardware as of yet.

Thanks, Richard. If you have the time, I, for one, would really appreciate it if you could share the results of your experiments in this area. It's not a place that a lot of people experiment in so it's always interesting to hear what others have found other than our own.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)