sqlbackup return parameter???

edward0724edward0724 Posts: 3
edited July 16, 2007 2:47AM in SQL Backup Previous Versions
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.
· Share on Twitter

Comments

  • Jason CookJason Cook Posts: 185
    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
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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 ...
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • edward0724edward0724 Posts: 3
    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
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.