0

My code ,for agent's job on SQL server 2008 , generate the backup file, but it keep OVERWRITE the first bak file everytime the agent's job triggered !!?

how can i make backup with different name time-related

e.g: testDB201313328.bak and after 1 minute create file with name : testDB201313329.bak

instead overwriting the first one

USE msdb ;
GO

DECLARE @fileName VARCHAR(90);

DECLARE @db_name VARCHAR(20);

DECLARE @fileDate VARCHAR(20);

DECLARE @commandtxt VARCHAR(100);


SET @fileName = 'C:\Test_Backups\'; 

SET @db_name = 'testDB';    

SET @fileDate = CONVERT(VARCHAR(8), GETDATE(),112) + convert (varchar(4),DATEPART(HOUR, GETDATE())) + convert ( varchar(4) ,DATEPART(MINUTE, GETDATE())) + convert ( varchar(4) ,DATEPART(SECOND, GETDATE()));

SET @fileName = @fileName + @db_name + RTRIM(@fileDate) + '.bak';

SET @commandtxt = 'BACKUP LOG testDB TO DISK =''' + @fileName + ''' WITH INIT'

-- add a job

EXEC dbo.sp_add_job 
@job_name = N'LogBackup',
@description =N'Log Backup on weekdays every 15 minutes from 8am till 6pm' ;

-- add job steps to job
EXEC sp_add_jobstep
@job_name = N'LogBackup',
@step_name = N'Weekdays_Log_Backup',
@subsystem = N'TSQL',
@command = @commandtxt ,
@on_success_action = 1,
@retry_attempts = 5,
@retry_interval = 1 ;
GO

...

Rami Alshoubaki
  • 67
  • 1
  • 2
  • 10

1 Answers1

2

First, backups can contain more than one backup set, so are you sure you are overwriting it and not just appending another set within the same file? Otherwise you need to add the time of day to your filename. Hope this helps. Also if its just one or two db, then there is an excellent (free for limited use) app, google SQLBackupAndFTP HTH

Ian P
  • 1,724
  • 1
  • 10
  • 12
  • The backup file name took the very first instance of variables including date and time and then overwrite it in the next triggers. the code: **SET @fileDate = CONVERT(VARCHAR(8), GETDATE(),112) + convert (varchar(4),DATEPART(HOUR, GETDATE())) + convert ( varchar(4) ,DATEPART(MINUTE, GETDATE())) + convert ( varchar(4) ,DATEPART(SECOND, GETDATE()));** ensure that file name is different everytime by including date and time. BTW... it's just an example .. so yes .. it's just one database – Rami Alshoubaki Mar 01 '13 at 15:37
  • Well DateTime goes to a thousanth of a second so if you just CONVERT( Varchar(25), GETDATE()) you will get yyyy mm dd hh:mm:ss:ttt, they will also sort by name in time order QED – Ian P Mar 04 '13 at 09:39