Refresh / recreate backup job

Paul Allen · May 14, 2008 5:09AM

We have created some sql backup jobs via the sqlbackup gui. The problem we have is that databases often get moved from 1 server to another. At the moment we are having to manually recreate the backup jobs via the gui if any databases have been moved.

Is there a way to automatically refresh the backup procs so that databases that have been removed are deleted from the backup proc, and when a database is added to a server this is by default added to the backup list.

If not is there a way to access/manipulate the list of databases that are selected to be backed up.

Apologies if there is a really obvious answer to this!

Many thanks
Paul

petey · May 14, 2008 5:24AM

You could use the BACKUP DATABASES/BACKUP LOGS syntax.

To back up all databases:

EXEC master..sqlbackup '-sql "BACKUP DATABASES &#91;*&#93; ... " '

To back up selected databases:

EXEC master..sqlbackup '-sql "BACKUP DATABASES &#91;pubs, northwind&#93; ... " '

To back up all databases except those named:

EXEC master..sqlbackup '-sql "BACKUP DATABASES EXCLUDE &#91;master, model, msdb&#93; ... " '

You might need to modify the T-SQL command manually for the SQL Server Agent backup job to get the results you want.

Paul Allen · May 14, 2008 5:50AM

Thanks v. much for the quick response.

I've no problem with modifying the T-SQL command within the SQL Server Agent backup job. Cheers for the syntax.

So that I can automate the modification of the T-SQL command do you happen to know if it is possible to access the list of databases that sqlbackup is set to backup?

petey · May 14, 2008 6:00AM

If you use the BACKUP DATABASES [*] or BACKUP DATABASES EXCLUDE syntax, SQL Backup reads information from master..sysdatabases, and uses the recovery model for each database to determine which databases to back up.

Refresh / recreate backup job

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk