January 25, 2016 at 11:24 am
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
January 25, 2016 at 11:42 am
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!
January 25, 2016 at 11:45 am
Would something like this work for you? I'm removing the loop and using a different method to concatenate as explained in here: http://qa.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.
January 25, 2016 at 11:53 am
Thanks very much for the replies. I've corrected the counter issue and will look the versions that Luis provided.
January 25, 2016 at 4:47 pm
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
January 25, 2016 at 6:39 pm
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.
January 25, 2016 at 6:45 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply