Save template without running a job?

DBA_Dave

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?

Jason Cook

Hi,
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,
Jason