What are the challenges you face when working across database platforms? Take the survey

Save template without running a job?

DBA_DaveDBA_Dave Posts: 31
edited September 25, 2007 11:22AM in SQL Backup Previous Versions
is there no way to create and save a template without actually executing a job?

Only the "Back Up" frame has the check box to save a template, and it only does that after it runs the job. There is no similar check box on the "Schedule Backup Jobs" frame.

It makes it very had to create a template of an 8-hour backup job if I have to come in to work at midnight and run the job by hand, hoping against hope that the console maintains its connection to the database until the job succeeds.

In other words, I can't make or use templates. Is there any chance that the developers can add that check box to the "Schedule Jobs" frame?
Dave Rutledge
Database Administrator
SNL Financial LC


  • Options
    Thanks for the suggestion, I'll pass this on to our developers and they will consider it for inclusion for a future release.

    There is a workaround that may be of some use:

    * Generate the backup steps using the "Backup Wizard", but don't start the backup.
    * On the "Script" tab at the end, select "Extended Stored Procedure" format.
    * Copy and paste this to notepad, and strip off the first part (any comments and the "EXECUTE master..sqlbackup" text), for example:
    N'-SQL "BACKUP DATABASE [MyDatabase] TO DISK = ''d:\folder\<AUTO>.sqb'' WITH COMPRESSION = 1"'

    * Now open Management Studio, and execute the following command on the SQL Server instance in question, filling in the blanks where appropriate:
    master..sqbutility 1052, '<name>', <settings_including_quotes>;

    So the above would become (and is pretty much what the User Interface does when it creates the template):
    master..sqbutility 1052, 'MySavedBackup', N'-SQL "BACKUP DATABASE [MyDatabase] TO DISK = ''d:\folder\<AUTO>.sqb'' WITH COMPRESSION = 1"';

    This will then appear the next time you open the backup wizard. This is not a "public" API so while it works in 5.0, 5.1 and 5.2; this behaviour may change in subsequent major releases.

    Hope that helps,
Sign In or Register to comment.