Capture results for sqlbackup into a temp table

jim.wilson

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.

petey

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"'