SINGLERESULTSET option in backup command for version 5

AlexGAlexG Posts: 24
edited March 3, 2010 10:39PM in SQL Backup Previous Versions
Hi,
does SINGLERESULTSET exist only in version 6
command bellow run with success on version 6.2
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [My DB] TO DISK = ''\\server01\dev\SQL2005\<AUTO>.sqb'' WITH COMPRESSION = 3, INIT, MAILTO_ONERROR = ''me@mysite.com;'', SINGLERESULTSET "'

and produce error on version5
SQL Backup v5.0.0.2770
Syntax error: 'SINGLERESULTSET' after ','


goal is to get 1 dataset in return

Thank you
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    IIRC, you need at least version 5.3 to use the SINGLERESULTSET option.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jalfanojalfano Posts: 24
    We are using SQLBackup version 5.4.0.55 and when I include the WITH SINGLERESULTSET parameter to the end of the command it is still giving me two result sets.

    One result set gives me the information about the backup file and the second is giving me exit code information just like when I don't use the WITH SINGLERESULTSET option.

    Is there something that I am missing? I need to get this working because we are making some custom restore scripts that require header information.

    Thanks,

    John
    · Share on Twitter
  • jalfanojalfano Posts: 24
    About 5 min after I posted it I realized I was putting the ending double quote right after the path which was excluding the WITH SINGLERESULTSET command.

    Now my problem is that in SQLBackup 6.0+ it works fine but in SQLBackup 5.4.0.55 I'm getting a syntax error saying that there's a syntax error SINGLERESULTSET after WITH. But it says this is supported since 5.3. Can anyone tell me if this is a mistake?

    John
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    The SINGLERESULTSET option is supported in version 5.3 and later. Are you using the SINGLERESULTSET option with the BACKUP command e.g.
    EXEC master..sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = &#91;e:\temp\pubs.sqb&#93; WITH SINGLERESULTSET"'
    or some other command?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jalfanojalfano Posts: 24
    I'm trying to use it for the RESTORE HEADERONLY command. I need to extract information from the header in order to do what I need to do.

    John
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    SINGLERESULTSET with RESTORE HEADERONLY works in 5.3.0.178 and later, so something like this:
    EXEC master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK =&#91;e:\temp\pubs.sqb&#93; WITH SINGLERESULTSET"'
    will return the header details in a single result set.

    Could you please check the version of SQBCoreService.exe that's installed for the SQL Server instance that's rejecting the SINGLERESULTSET option?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jalfanojalfano Posts: 24
    I ran the exact code you posted above and just replaced the path with a path to one of our files. I'm still getting the same error "Syntax error: 'SINGLERESULTSET' after 'WITH'"

    File version of SQBCoreService.exe is 5.1.0.2781

    When I go to help > about it says I'm using Red-Gate SQLBackup 5.4.0.55

    Windows version is Windows Server 2003 Enterprise Edition x64 SP2

    SQL Server is SQL Server 2005 Standard Edition x64 SP3 CU1
    (9.0.4207)

    Please let me know if you need any other information.

    Thanks!

    John
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    The version of SQBCoreService.exe you are using needs to be 5.3.0.178 or newer. To upgrade this file:

    - locate the SQBServerSetup.exe file on any workstation that has the SQL Backup GUI installed. The file will be located in the SQL Backup installation folder. The version should be 5.4.0.55, since that's the version of your GUI.

    - copy SQBServerSetup.exe to the box running your SQL Server instance

    - ensure no SQL Backup processes are running, and if possible, no SQL Backup GUIs are connected to that SQL Server instance.

    - run SQBServerSetup.exe on the box

    This will upgrade the SQL Backup Agent service, the command line interface, and the extended stored procedures to version 5.4.0.55.

    Thanks.
    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.