July 5, 2011 at 1:36 pm
I typically create a maintenance plan and choose the backup all user databases option. The plan usually contains a task to delete any old backups older than x amount of days. It appears that SQL Server backs up all the user databases before deleting any of the old backups. Is there any sort of option to have SQL Server delete the old backup for a database before moving on and backing up the second database? We're getting tight on storage so it's hard to keep the additional storage required to first accomplish all the backups before releasing storage as the old backups are deleted. I know I can create a separate backup and delete task for each database but I'd prefer to stick with just one task to backup all. Any help would be appreciated.
Thanks
July 5, 2011 at 3:02 pm
This cannot be done using the standard tasks provided for maintenance plans. To do this, you would need to write custom code to loop through each database - backup the database, then delete previous backups when that is complete.
That code could then be called from an Execute SQL Task in the maintenance plan or scheduled from a job step in an agent job.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 5, 2011 at 3:38 pm
you might try something like this, you add as needed....
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupDbWithTs] Script Date: 07/05/2011 16:23:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[BackupDbWithTs]
@db_name sysname
,@folder nvarchar(255)
,@backup_type varchar(13)
,@backup_extension varchar(10)
,@with_checksum char(1) = 'Y'
,@do_verification char(1) = 'Y'
AS
DECLARE @sql nvarchar(4000)
DECLARE @filename nvarchar(255)
DECLARE @full_path_and_filename nvarchar(1000)
DECLARE @err_msg nvarchar(2000)
DECLARE @crlf varchar(2)
SET @crlf = CHAR(13) + CHAR(10)
--Verify valid backup type
IF @backup_type NOT IN('DATABASE', 'LOG', 'DIFFERENTIAL')
BEGIN
SET @err_msg = 'Backup type ' + @backup_type + 'is not valid. Allowed values are DATABASE, LOG and DIFFERENTIAL'
RAISERROR(@err_msg, 16, 1)
RETURN -101
END
--Make sure folder name ends with '\'
IF RIGHT(@folder, 1) <> '\'
SET @folder = @folder + '\'
--Make file extension starts with '.'
IF LEFT(@backup_extension, 1) <> '.'
SET @backup_extension = '.' + @backup_extension
--Construct filename
SET @filename = @db_name + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(char(16), CURRENT_TIMESTAMP, 120), '-', ''), ' ', ''), ':', '')
--Construct full path and file name
SET @full_path_and_filename = @folder + @filename + @backup_extension
--Construct backup command
SET @sql = 'BACKUP ' + CASE @backup_type WHEN 'LOG' THEN 'LOG' ELSE 'DATABASE' END + ' ' + QUOTENAME(@db_name) + @crlf
SET @sql = @sql + 'TO DISK = ' + QUOTENAME(@full_path_and_filename,'''') + @crlf
SET @sql = @sql + 'WITH ' + @crlf
SET @sql = @sql + 'RETAINDAYS=1,' + @crlf
SET @sql = @sql + ' INIT,' + @crlf
SET @sql = @sql + ' NAME = ' + QUOTENAME(@filename,'''') + ',' + @crlf
SET @sql = @sql + 'COMPRESSION,' + @crlf
IF @backup_type = 'DIFFERENTIAL'
SET @sql = @sql + ' DIFFERENTIAL,' + @crlf
LSBackup_te_control
IF @with_checksum <> 'N'
SET @sql = @sql + ' CHECKSUM,' + @crlf
--Add backup option below if you want to!!!
--Remove trailing comma and CRLF
SET @sql = LEFT(@sql, LEN(@sql) - 3)
--PRINT @sql
EXEC(@sql)
IF @do_verification = 'Y'
RESTORE VERIFYONLY FROM DISK = @full_path_and_filename
----sample of how to call it
EXEC BackupDbWithTs 'your_database', 'F:\Backup', 'Database','bak', 'Y', 'N'
July 6, 2011 at 2:17 am
you can also delete the current backup before taking the new but this is not recommended for obvious reasons
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 7, 2011 at 12:42 pm
use backup compression
July 11, 2011 at 8:45 am
instead of having one task to backup all databases and one task to clear them out, I would create seperate MP's for each database and run them staggered
or split out the tasks in the same MP and do Backup DB1, Clear File from DB1, Backup DB2.............................
seperate MP's are my personal preference as if the MP should fail, you dont have to backup all the databases again
July 12, 2011 at 6:48 am
Stop using maintenance plans. go to ola.hallengren.com and use the FREE and fully documented goodness there! Awesome stuff.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 12, 2011 at 6:55 am
TheSQLGuru (7/12/2011)
Stop using maintenance plans.
I concur, use well scripted jobs instead
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 20, 2011 at 10:11 pm
You shouldn't be running this low on any drive, not even it's "just" your backup.
What if something crache, you get you DB partially online and need to restore the backup to recover some data.
Having a backup is half the process of recovery. Being able to restore it within your SLA would also be a crucial step.
Logically if this is your test server then it's not needed, or is it.
Cheers
Jannie
July 29, 2011 at 7:38 am
TheSQLGuru (7/12/2011)
Stop using maintenance plans. go to ola.hallengren.com and use the FREE and fully documented goodness there! Awesome stuff.
Completely agree! Ola's maintenance scripts are very valuable!
July 29, 2011 at 4:34 pm
brent.kraus (7/29/2011)
TheSQLGuru (7/12/2011)
Stop using maintenance plans. go to ola.hallengren.com and use the FREE and fully documented goodness there! Awesome stuff.Completely agree! Ola's maintenance scripts are very valuable!
And exactly how does switching to scripts solve the OP's problem? I was not aware that Ola's scripts had a built in cleanup utility that removes old files after each database is backed up.
As far as I recall - these utilities suffer the same problem as maintenance plans. If you backup more than one database at a time, the cleanup doesn't happen until after all databases are backed up.
With that said - I will say that I use maintenance plans all the time. I do not have any problems with them and I am able to do everything that Ola's utilities do with no problems. Are there problems with maintenance plans - yes, but almost every case where this is now true - we find that the ultimate cause is either a system that hasn't been upgraded or a client that has not been upgraded.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2012 at 10:51 am
Found this script its really nice.
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
From : http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply