Capture results for sqlbackup into a temp table

jim.wilsonjim.wilson Posts: 8
edited March 31, 2009 9:28PM in SQL Backup Previous Versions
Running version 5.2.0.2825

I'd like to capture the results of the backup process to a temp table. I found some suggested code for doing this, but I'm getting the error "Column name or number of supplied values does not match table definition".

create table #resultstring (message varchar (2500))

Select @cmd2 = '-SQL "BACKUP DATABASE [dbname] TO DISK = ''c:\sql\Backup\dbname_db_200903301045.sqb'' WITH THREADCOUNT = 3, COMPRESSION = 1, MAXTRANSFERSIZE = 1048576, VERIFY"'

Insert into #resultstring Exec master.dbo.sqlbackup @cmd2

select * from #resultstring

When I run this without the code for the temp table, it looks like the output is in two different formats; the top part looks to be one large column, and the bottom part looks to be two columns. If the output is in fact two different formats, how could this have ever worked for anyone?

It seems like there should be a way to direct this output to a table. I'd like to avoid creating an output file and then reading that file back into a temp table, which at this point looks like my only choice.

Comments

  • peteypetey Posts: 2,358 New member
    You need to use the SINGLERESULTSET option e.g.

    Select @cmd2 = '-SQL "BACKUP DATABASE [dbname] TO DISK = ''c:\sql\Backup\dbname_db_200903301045.sqb'' WITH THREADCOUNT = 3, COMPRESSION = 1, MAXTRANSFERSIZE = 1048576, VERIFY, SINGLERESULTSET"'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.