Backup/Zip Operation: Too many resultsets

  • 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

  • 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

  • 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