November 16, 2005 at 9:45 am
Hello all,
I only have about 2.5 years experiance at SQL Server Administration. I have been using Database Maintenance Plans and SQL Server Agent jobs to perform db backups & trans log backups. It has come to my attention that a full db backup at night and 2 to 3 differential backups during the day would be more efficient than 3 full backups during a 24 hr. period.
However when you use the db Maintenance Plan you have the ability to select that you want to delete old backup files, say after 2 or 3 days. I have studied the syntax that the maintenance plan places into the job and believe that I could place it into a job that performs a Differential backup, however, I don't understand how to make the differential backup job change the file name with the date time exp: diffbakup_200511160859.bak to diffbakup_200511161603.bak
The maint. plan does this and then you can delete old files. But when you build a job using enterprise manager, secondary click the db, all tasks, and choose db backup once you have the settings set, and choose to schedule the file name never changes. And, it continues to grow as it appends data and you have no instruction to delete it when it ages.
Sorry for the long story, wanted to make sure I explained myself well. Any help will be most appreaciated!
Thanks,
November 17, 2005 at 6:58 am
You can change the backup name by using sql statements
/*
Backup file name concatenated with date & time as yyyymmdd_hhnnss
*/
DECLARE @DtTime varchar(30)
DECLARE @FileName varchar(100)
SET @dtTime = CONVERT(varchar(20),getdate(),112) + '_' + SUBSTRING(CONVERT(varchar(20),getdate(),120),12,10)
SET @dtTime = REPLACE(@dtTime,':','')
SET @FileName = 'C:\Master' + RTRIM(@dtTime) + '.TRN'
BACKUP DATABASE Master TO DISK = @FileName
To delete old backups, I use ActiveX scripts using the FileSystemObject and looping through the files in the backup folder.
November 17, 2005 at 7:37 am
Quite a few of the backup scripts in the script library on this site do this.
November 17, 2005 at 7:43 am
Sample script to do differential backups of all databases databases in simple recovery mode
--Does a Differential back of all databases that are in Simple Recovery mode, excluding tempdb and master.
DECLARE
@Database sysname,
@File Varchar(1000),
@Cmd Varchar(1000)
--Get list of databases (Status & 8 = 8 equals truncate log on checkpoint, ie simple recovery)
DECLARE Databases CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE Status & 8 = 8 and name NOT IN ('tempdb','master') --Databases set to truncate log on checkpoint (simple recovery)
OPEN Databases
--Process each database
FETCH NEXT FROM Databases INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
--Find file name and location of last database backup and reuse the file name and path (Changes _db_ to _dif_ and sets YYYYMMDDHHMM)
--Only retrieves files where the end of the name is numeric ie YYYYMMDDHHMM
SELECT TOP 1 @File =
physical_device_name
FROM msdb.dbo.backupset S JOIN msdb.dbo.backupmediafamily M
ON S.media_set_id = M.media_set_id
WHERE backup_finish_date > getdate()-30 --Backups in the last 30 days
AND type = 'D' --Database Backup
AND database_name = @Database --Database from the cursor
AND ISNUMERIC(LEFT(RIGHT(physical_device_name, 16),12)) = 1 --Where file name ends YYYYMMDDHHMM
ORDER BY backup_finish_date DESC --Most recent backup
--Change file name (date)
SET @File =
Replace(
@File,
LEFT(RIGHT(@File, 16),12),
CAST(DATEPART(YYYY, GETDATE())AS CHAR(4))+
RIGHT('0'+CAST(DATEPART(MM, GETDATE())AS VARCHAR(2)),2)+
RIGHT('0'+CAST(DATEPART(DD, GETDATE())AS VARCHAR(2)),2)+
RIGHT('0'+CAST(DATEPART(HH, GETDATE())AS VARCHAR(2)),2)+
RIGHT('0'+CAST(DATEPART(mi, GETDATE())AS VARCHAR(2)),2))
--Change file name _db_ to _dif_
SET @File =
Replace(@File, '_db_', '_dif_')
--Backup
SET @Cmd = 'BACKUP DATABASE '+@Database+' TO DISK = '''+@File+''' WITH DIFFERENTIAL'
PRINT @Cmd
EXEC (@Cmd)
--Get next Database
FETCH NEXT FROM Databases INTO @Database
END
CLOSE Databases
DEALLOCATE Databases
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply