April 26, 2004 at 2:22 am
Hi everyone
I have the folowing situations with my backups.
With Database Maintenance Plan is created backup strategy. Full Backup is performing every day at 1.00 am. And Romove files older than: is checked on 2 days, and it's working fine.
I am interested how can i perform this action with tsql, without using DMPlan.
I tryed this:
BACKUP DATABASE TEST TO DISK 'C:...' WITH INIT, RETAINDAYS = 2
I put this statement into job but it is not working. I tryed after that with (WITH NOINIT) but in that case .bak file grow and files older than 2 days are not deleted.
How can i delete files older then 2 days.
Thanks
April 26, 2004 at 8:37 am
Your maintenance plan is making separate backup files. Your TSQL plan is using only one backup file. The issue is that when the backup is appended to an existing file vice creating a new file, the date of the file changes. It is the date of the actual file that is used with the RETAIN option.
Using TSQL you would need to create a new file each day. I have seen suggestions on how to append the date to the backup file, but I can't remember how it was done.
You could do a search and find the answer (it would probably be in the BACKUP forum which is more appropriate for this.)
-SQLBill
April 26, 2004 at 10:28 am
There are any number of solutions. I do the backups with TSQL and the file deletions with VBScript in an ActiveX task. Both are contained in a DTS package which manages the locations, etc.
This has examples of how to handle backups, albeit it used SQL Litespeed instead of native backup. http://www.sqlservercentral.com/scripts/contributions/1135.asp
Or this one: http://www.sqlservercentral.com/scripts/contributions/1126.asp
This one (haven't tested) is supposed to delete the old ones: http://www.sqlservercentral.com/scripts/contributions/767.asp
April 27, 2004 at 2:13 am
In order to delete old backup file You can try to use this:
DECLARE @TrnBckFileToDelete as varchar(255)
DECLARE ExpiredTranLog_CURSOR CURSOR FOR
SELECT msdb..backupmediafamily.physical_device_name
FROM msdb..backupset INNER JOIN msdb..backupmediafamily
ON msdb..backupset.media_set_id = msdb..backupmediafamily.media_set_id
WHERE msdb..backupset.expiration_date < CURRENT_TIMESTAMP
OPEN ExpiredTranLog_CURSOR
FETCH NEXT FROM ExpiredTranLog_CURSOR INTO @TrnBckFileToDelete
WHILE @@fetch_status = 0
BEGIN
SET @cmdstr = 'DEL "' + @TrnBckFileToDelete + '"'
EXEC @cmdmsg = master..xp_cmdshell @cmdstr, NO_OUTPUT
FETCH NEXT FROM ExpiredTranLog_CURSOR INTO @TrnBckFileToDelete
END
CLOSE ExpiredTranLog_CURSOR
DEALLOCATE ExpiredTranLog_CURSOR
HTH
Franco
April 27, 2004 at 6:02 am
I've wrttien this Stored Proc to do Differential backups and delete old backus after a set numberof days. It also truncates & shrinks the Tlog
-- =============================================
-- Differential Backup 'YourDatabase' of Database.
-- Backups 2 days old will be automatically
-- deleted by this SP if the backup succeeds.
-- =============================================
CREATE PROCEDURE USP_Backup_Elf_With_Differntial AS
DECLARE @BackupName nchar(39)
DECLARE @BackupName1 nchar(23)
DECLARE @BackupName2 nchar(08)
DECLARE @BackupName3 nchar(4)
DECLARE @BackupName4 nchar(4)
DECLARE @DelBackupDay nchar(8)
DECLARE @Date datetime
Declare @Date2 datetime
Declare @String nchar(75)
SET @backupname1 = 'D:\YourDatabaseName_Diff_'
SET @backupname2 = CONVERT(char(8),getdate(),112)
SET @backupname3 = LEFT(REPLACE(CONVERT(char(8),getdate(),108),':',''),4)
SET @backupname4 = '.bak'
set @backupname = @backupname1 + @backupname2 + @backupname3 + @backupname4
BACKUP DATABASE [YourDatabaseName] TO DISK = @backupname WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'YourDatabaseName Diff backup', NOSKIP , STATS = 10, NOFORMAT
Begin
If @@error = 0
Begin
SET @Date = getdate()
SET @date2 = DATEADD(dd, -2, @Date)
SET @DelBackupDay = CONVERT(char(8),@date2, 112)
SET @backupname = @backupname1 + left(@DelBackupDay,8) + @backupname3 + @backupname4
SET @String = 'exec master..xp_cmdshell ''' + 'DEL ' + @backupname + ''''
exec master..sp_executesql @String -- This will delete the Differential Backup from 2 days ago.
backup log YourDatabaseName with truncate_only
DBCC SHRINKDATABASE (N'YourDatabaseName', 0,TRUNCATEONLY)
If DateName(dw,getdate()) = 'Monday' -- This will delete the Differential Backup from Friday Night
Begin
SET @date2 = DATEADD(dd, -3, @Date)
SET @DelBackupDay = CONVERT(char(8),@date2, 112)
SET @backupname = @backupname1 + left(@DelBackupDay,8) + @backupname3 + @backupname4
SET @String = 'exec master..xp_cmdshell ''' + 'DEL ' + @backupname + ''''
exec master..sp_executesql @String
End
End
Else
exec master..xp_sendmail 'DBA@MailSever', 'Differential Backup Failed on ELF Database'
End
GO
April 28, 2004 at 12:34 am
Thanks a lot
Alex
July 16, 2004 at 10:43 am
When attempting to make the above script dynamic in order to generate differential backups for each user database: I encounter a MSG 3021 error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a backup or restore operation within a transaction.
Below is a sample of the script
create PROCEDURE new_test2_Backup_With_Differential AS
Declare @RUNID bigint
Declare @JOBID bigint
Declare @StepID bigint
Declare @TaskID bigint
Declare @calledby varchar(20)
Declare @rc bigint
Declare @err bigint
DECLARE @BackupName nchar(94)
DECLARE @BackupName1 nchar(62)
DECLARE @BackupName2 nchar(08)
DECLARE @BackupName3 nchar(4)
DECLARE @BackupName4 nchar(4)
DECLARE @DelBackupDay nchar(8)
DECLARE @Date datetime
Declare @Date2 datetime
Declare @Date14 datetime
Declare @String nchar(125)
Declare @String1 nchar(125)
Declare @l_dbname varchar(40)
Declare @trim_dbname varchar(40)
declare @l_FETCH_STATUS bigint
DECLARE @DOScmnd varchar(255)
DECLARE @From varchar(100)
DECLARE @To varchar(100)
DECLARE @Subject varchar(100)
DECLARE @Body varchar(4000)
DECLARE @sqlText varchar(4000)
DECLARE @SQLcmnd nvarchar(255)
declare @v_DBtoBackup bigint
DECLARE @lcursor CURSOR
set @v_DBtoBackup = 0
SET @lcursor = CURSOR FOR
SELECT db_name(dbid)
FROM [master].[dbo].[sysdatabases]
WHERE dbid > 5
ORDER BY dbid asc
OPEN @lcursor
FETCH NEXT FROM @lcursor INTO
@l_dbname
SET @l_FETCH_STATUS = @@FETCH_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
SET @trim_dbname = RTRIM(@l_dbname)
--SET @backupname1 = 'E:\BIS\dbbackups\'+@trim_dbname+'\'+@trim_dbname+'_Diff_'
SET @backupname1 = 'C:\temp\'+@trim_dbname+'_Diff_'
--SET @backupname1 = 'E:\BIS\dbbackups\SunClinicalCodeInfo\SunClinicalCodeInfo_Diff_'
SET @backupname2 = CONVERT(char(8),getdate(),112)
SET @backupname3 = LEFT(REPLACE(CONVERT(char(8),getdate(),108),':',''),4)
SET @backupname4 = '.bak'
set @backupname = rtrim(@backupname1) + @backupname2 + @backupname3 + @backupname4
--BACKUP DATABASE SCContentMapping TO DISK = @backupname WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'YourDatabaseName Diff backup', NOSKIP , STATS = 10, NOFORMAT
--BACKUP DATABASE SunClinicalCodeInfo TO DISK = @backupname WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'YourDatabaseName Diff backup', NOSKIP , STATS = 10, NOFORMAT
--BACKUP DATABASE + @l_dbname TO DISK = @backupname WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'YourDatabaseName Diff backup', NOSKIP , STATS = 10, NOFORMAT
--set @SQLcmnd = 'BACKUP DATABASE ' + @l_dbname + ' TO DISK = '+ @backupname+ ' WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'+@l_dbname+ ' Diff Diff backup', NOSKIP , STATS = 10, NOFORMAT'
--set @SQLcmnd = 'BACKUP DATABASE ' + @trim_dbname + ' TO DISK = '''+ rtrim(@backupname)+ ''' WITH INIT , NOUNLOAD, NOSKIP , STATS = 10 '
set @SQLcmnd = 'BACKUP DATABASE ' + @trim_dbname + ' TO DISK = '''+ rtrim(@backupname)+ ' '' WITH INIT , NOUNLOAD, NOSKIP , STATS = 10 '
PRINT 'Statement to be executed: ' + CAST(@SQLcmnd AS VARCHAR(400))
EXECUTE @rc = sp_executesql @SQLcmnd
--BACKUP DATABASE Northwind TO DISK = 'C:\temp\Northwind_Diff_200407151658.bak ' WITH INIT , NOUNLOAD, NOSKIP , STATS = 10
set @err = @@error
July 17, 2004 at 3:32 pm
I would take a look at the backup scripts already written here:
http://weblogs.sqlteam.com/tarad
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply