I have a few databases greater than 2GB, so this saves me quite a lot on DiskSync requirements. Instead of 2GB * 7 days == 14GB, I have 2GB with seven days of delta blocks each day (probably no more than 100MB a day)
I would probably stick this stored procedure in your master database, then schedule a nighly job through the SQL Server Agent to run this a little before your DiskSync kicks in. I don't see a lot of potential for abuse, but you should verify that only the account running the SQL agent has execute permission on this procedure.
You will probably still want to keep the maintenance plan around for the database optimizations and integrity checks and stuff, since this script doesn't deal with any of that.
Also, please be experienced enough to examine the backup folder and make sure this script runs. If you're not comfortable enough with MS-SQL to know what I'm talking about here, you should probably stick with the maintenance plans for backup. Read: I'm not responsible for any loss of data incurred by relying on this script.
Execute this code through your query analyzer while attached to a database (I suggest 'master')
CODE
CREATE Procedure spBackupAlldatabases
as
declare cur_databases cursor for
select name from master..sysdatabases where name not in ('tempdb')
open cur_databases
declare @spath varchar(100), @sDBName varchar(100), @sBackupPath varchar(100), @cleardbs varchar(100)
set @sPath = 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUP'
-- Clear out backups in this folder
-- Uncomment if you like (clears out databases that have since been removed)
-- However this may defeat the purpose of this script, since the creation dates will have changed on all files
-- set @cleardbs = 'del "' + @sPath + '*.bak"'
-- exec master..xp_cmdshell @cleardbs
fetch next from cur_databases into @sDBName
While (@@fetch_status = 0)
BEGIN
set @sBackupPath = @sPath + @sDBName + '.bak'
backup database @sDBName to disk = @sBackupPath with init
fetch next from cur_databases into @sDBName
END
close cur_databases
deallocate cur_databases
GO
as
declare cur_databases cursor for
select name from master..sysdatabases where name not in ('tempdb')
open cur_databases
declare @spath varchar(100), @sDBName varchar(100), @sBackupPath varchar(100), @cleardbs varchar(100)
set @sPath = 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUP'
-- Clear out backups in this folder
-- Uncomment if you like (clears out databases that have since been removed)
-- However this may defeat the purpose of this script, since the creation dates will have changed on all files
-- set @cleardbs = 'del "' + @sPath + '*.bak"'
-- exec master..xp_cmdshell @cleardbs
fetch next from cur_databases into @sDBName
While (@@fetch_status = 0)
BEGIN
set @sBackupPath = @sPath + @sDBName + '.bak'
backup database @sDBName to disk = @sBackupPath with init
fetch next from cur_databases into @sDBName
END
close cur_databases
deallocate cur_databases
GO
Then schedule a task that is simply
CODE
Exec spBackupAlldatabases
