sqlbackup return parameter???

edward0724

How do I capture result return by redgate sql backup command - sqlbackup

I am doing something like this:

insert into #result
exec sqlback @backupdb

but I am getting insert error:

Insert Error: Column name or number of supplied values does not match table definition.

Can anyone tell me the how many column does the sqlbackup return and what are their datatype. or anything other way to do this.

Thanks in advance.

Jason Cook

SQL Backup 4 and 5 returns two integer values from the extended stored procedure, the first is the SQL Backup exit code, the second is the SQL Server error code.

When the SQL Backup statement is executed, you can extract them in the following way (where @var1 and @var2 are declared "int" variables):

execute master..sqlbackup '-SQL "BACKUP DATABASE [master] TO DISK = ''c:\temp\master.sqb'' WITH INIT"', @var1 OUTPUT, @var2 OUTPUT

If you wanted to use this with temporary tables, the definition you would need would be something like the following:

CREATE TABLE #temp (sqbexitcode int, sqlservercode int);

Unfortunately there is no easy/trivial way to capture the textual output from the extended stored procedure in 4.x, 5.0 or 5.1... but we will be introducing a new function for this in 5.2 so that this information can also be captured easily.

Hope that helps,
Jason

petey

If you are trying to store the contents of the output into a table, it's not currently possible because SQL Backup returns 2 result sets. The SELECT ... INTO ... FROM ... command won't work because both result sets have a different structure.

We're currently testing an option to omit the second result set, so that the output is similar to that produced in version 3. Stay tuned ...

edward0724

I know I could specify parameters like the following, my question are

SQL "Transact-SQL_statement" -U sa -U password -I instance

1) do we have a parameter that will write the output to a file
2)

PostPosted: Sat Jul 14, 2007 7:08 am Post subject:
If you are trying to store the contents of the output into a table, it's not currently possible because SQL Backup returns 2 result sets. The SELECT ... INTO ... FROM ... command won't work because both result sets have a different structure.

We're currently testing an option to omit the second result set, so that the output is similar to that produced in version 3. Stay tuned ...

will this be available on sql backup 5, if not when/which version

petey

By default, SQL Backup logs all output for a backup/restore process. The default folder where these log files are found is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>.

You can use the LOGTO option to save the log file in another folder, using your own naming convention.