February 21, 2011 at 1:50 pm
I have a SQL Server 2005 box with about 300 databases on it. The server doesn't have enough space for all 300 databases with a day's worth of backups, and yet we must have the database as well as one day's backup kept locally. Also we don't have any of those nifty backup/zip utilities.
As a result I believe I'm stuck with performing a backup operation followed immediately by a command line zip operation. I'm running into a problem with the job exceeding the number of recordsets allowed because the xp_cmdshell spits back any command line output as a result. Is there any way around this? Code sample below:
declare @databasename varchar(100)
declare @date varchar(8)
declare @time varchar(12)
declare @BackupPath varchar(100)
declare @7zipPath varchar(100)
set @date = CONVERT(CHAR(8),GETDATE(),112)
set @BackupPath = 'D:\MSSQL\Backup\'
set @7zipPath = 'D:\SQL_Work\NightlyBackupMover\7za.exe'
declare DatabaseCursor cursor for
select name from sys.databases
where name<>'tempdb'
order by name
open DatabaseCursor
fetch next from DatabaseCursor into @databasename
while @@fetch_status=0
begin
if @databasename in ('master','model','msdb','distribution')
begin
set @time = CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(5),GETDATE(),108),':','')
exec('backup database ['+@databasename+'] to disk='''+@BackupPath+'SystemDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
if @@error<>0
begin
WAITFOR DELAY '00:02'
set @time = CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(5),GETDATE(),108),':','')
exec('backup database ['+@databasename+'] to disk='''+@BackupPath+'SystemDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
end
if @@error<>0
begin
WAITFOR DELAY '00:02'
set @time = CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(5),GETDATE(),108),':','')
exec('backup database ['+@databasename+'] to disk='''+@BackupPath+'SystemDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
end
exec('master..xp_cmdshell '''+@7zipPath+' a -mmt=3 -mx=3 '+@BackupPath+'SystemDB\'+@databasename+'\'+@databasename+'_'+@date+'.7z '+@BackupPath+'SystemDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
exec('master..xp_cmdshell ''del '+@BackupPath+'SystemDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
end
else
begin
set @time = CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(5),GETDATE(),108),':','')
exec('backup database ['+@databasename+'] to disk='''+@BackupPath+'UserDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
if @@error<>0
begin
WAITFOR DELAY '00:02'
set @time = CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(5),GETDATE(),108),':','')
exec('backup database ['+@databasename+'] to disk='''+@BackupPath+'UserDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
end
if @@error<>0
begin
WAITFOR DELAY '00:02'
set @time = CONVERT(CHAR(8),GETDATE(),112)+REPLACE(CONVERT(CHAR(5),GETDATE(),108),':','')
exec('backup database ['+@databasename+'] to disk='''+@BackupPath+'UserDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
end
exec('master..xp_cmdshell '''+@7zipPath+' a -mmt=3 -mx=3 '+@BackupPath+'UserDB\'+@databasename+'\'+@databasename+'_'+@date+'.7z '+@BackupPath+'UserDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
exec('master..xp_cmdshell ''del '+@BackupPath+'UserDB\'+@databasename+'\'+@databasename+'_backup_'+@time+'.bak''')
end
fetch next from DatabaseCursor into @databasename
end
close DatabaseCursor
deallocate DatabaseCursor
February 21, 2011 at 2:22 pm
xp_cmdshell has a "no_output" option. Use that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 21, 2011 at 2:40 pm
doh! Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply