Refresh / recreate backup job

Paul AllenPaul Allen Posts: 3
edited May 14, 2008 6:00AM in SQL Backup Previous Versions
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
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    You could use the BACKUP DATABASES/BACKUP LOGS syntax.

    To back up all databases:
    EXEC master..sqlbackup '-sql "BACKUP DATABASES [*] ... " '
    To back up selected databases:
    EXEC master..sqlbackup '-sql "BACKUP DATABASES [pubs, northwind] ... " '
    To back up all databases except those named:
    EXEC master..sqlbackup '-sql "BACKUP DATABASES EXCLUDE [master, model, msdb] ... " '
    You might need to modify the T-SQL command manually for the SQL Server Agent backup job to get the results you want.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Paul AllenPaul Allen Posts: 3
    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?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    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.