Backup DB task in SQL 2005 Maintenance Plan

Question

I have a Backup DB task setup to do a full backup of All User Databases. However, I find that when new databases are added, they do not get automatically included in the backup plan.

I am forced to open the plan each time a new database is added and select All User Databases Radio button and save the package for the new DB to be added to the backup task.

Also the New DB does not show up in the SelectedDatabases collection in the properties for the backup task.

Any ideas?

Thanks, Raj

Answer 1

Write an SQL script that iterates over all databases and schedule a job to run this script regulary Here is a sample

declare cur_databases cursor
for select name from sysdatabases
where not name in ('master', 'model', 'tempdb', 'msdb')

declare @sql nvarchar(1000)
declare @name nvarchar(100)

open cur_databases

fetch cur_databases into @name

while (@@fetch_status = 0)
begin


    select @sql = 'BACKUP DATABASE [' + @name + '] TO  DISK = N''D:\Backup\' + @name + '.bak'''  +
    ' WITH NOFORMAT, NOINIT, NAME = N''' + @name + ' Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 '

    print @sql
    exec (@sql)


    fetch cur_databases into @name

end

close cur_databases
deallocate cur_databases

Answer 2

There's something strange about the configuration of your plan as it's fine to add new databases - they get picked up automatically next time the plan runs. At least it does on the servers I look after.

Who is the 'owner' of each database - could it be a permissions issue which is preventing your job from seeing these new databases as they are owned by another user?

Answer 3

This is a bug, and fixed in SP3, see this document