Capture results for sqlbackup into a temp table
jim.wilson
Posts: 8
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.
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
Select @cmd2 = '-SQL "BACKUP DATABASE [dbname] TO DISK = ''c:\sql\Backup\dbname_db_200903301045.sqb'' WITH THREADCOUNT = 3, COMPRESSION = 1, MAXTRANSFERSIZE = 1048576, VERIFY, SINGLERESULTSET"'
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8