How to create a new backup file with a unique name

Question

0.00/5 (No votes)

See more:

i am trying to take a backup by creating a job in sql server agent using stored procedure. i am able to take the backup but each time it overwrites my previous backup file. i want my backup should create a different file with unique file name using date. how can I achieve this. below is my code, any help would be appreciated

What I have tried:

use msdb;
go
EXEC msdb.dbo.sp_add_job  
   @job_name = 'BackupDatabaseLog',      
   @description = 'Procedure execution every 15min' ; 
   GO
  
DECLARE @backupfilename varchar(1000)
DECLARE @path varchar(1000)
set @backupfilename='C:\SqlBackups\AdventureWorks\Log\trans' + CONVERT (VarChar, GetDate(), 112) + '.bak with NOINIT, NOFORMAT'
set @path ='BACKUP Log AdventureWorks2012 TO  DISK ='''+ @backupfilename + ''''
EXEC msdb.dbo.sp_add_jobstep  
    @job_name = 'BackupDatabaseLog',   
    @step_name = 'Log Backup',   
    @subsystem = 'TSQL',   
   @command = @path;
GO

 EXEC msdb.dbo.sp_add_schedule  
    @schedule_name = 'Log schedule',   
    @freq_type = 4,  
    @freq_interval = 1,
	@freq_subday_type=4, 
    @freq_subday_interval=15,
	@active_start_date=20181007,
	--@freq_recurrence_factor=1,
    @active_start_time = 080000,
	@active_end_time=180000;   

 EXEC msdb.dbo.sp_attach_schedule  
   @job_name = 'BackupDatabaseLog',  
   @schedule_name = 'Log schedule' ;

    EXEC msdb.dbo.sp_add_jobserver  
   @job_name = 'BackupDatabaseLog',  
   @server_name = @@servername ;
   GO

Posted 7-Oct-18 12:36pm

Simerjit1

Updated 8-Oct-18 8:09am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

summiya1 · Answer 1 · 2018-10-08T08:10:00

Try this

SET NOCOUNT ON;

DECLARE 
      @FileName NVARCHAR(1024)
    , @DBName NVARCHAR(256)
    , @PathName NVARCHAR(256)
    , @Message NVARCHAR(2048)
    , @IsCompressed BIT

SELECT 
      @PathName = 'D:\BACKUP\'
    , @IsCompressed = 1 

DECLARE db CURSOR LOCAL READ_ONLY FAST_FORWARD FOR  
    SELECT
          sd.name
        , file_path = @PathName + FileDate + '_' + name + '.bak'
    FROM sys.databases sd
    CROSS JOIN (
        SELECT FileDate = 'ABCD_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '_')
    ) fd
    WHERE sd.state_desc != 'OFFLINE'
        AND sd.name NOT IN ('master', 'model', 'msdb', 'tempdb')
    ORDER BY sd.name 

OPEN db

FETCH NEXT FROM db INTO 
      @DBName
    , @FileName  

WHILE @@FETCH_STATUS = 0 BEGIN 

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @Message = REPLICATE('-', 80) + CHAR(13) + CONVERT(VARCHAR(20), GETDATE(), 120) + N': ' + @DBName
    RAISERROR (@Message, 0, 1) WITH NOWAIT

    SELECT @SQL = 
    'BACKUP DATABASE [' + @DBName + ']
    TO DISK = N''' + @FileName + '''
    WITH FORMAT, ' + CASE WHEN @IsCompressed = 1 THEN N'COMPRESSION, ' ELSE '' END + N'INIT, STATS = 15;' 

    EXEC sys.sp_executesql @SQL

    FETCH NEXT FROM db INTO 
          @DBName
        , @FileName 

END   

CLOSE db   
DEALLOCATE db

Output will be

BACKUP DATABASE [AdventureWorks2008R2]
    TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2008R2.bak'
    WITH FORMAT, COMPRESSION, INIT, STATS = 15;

BACKUP DATABASE [AdventureWorks2008R2_Live]
    TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2008R2_Live.bak'
    WITH FORMAT, COMPRESSION, INIT, STATS = 15;

BACKUP DATABASE [AdventureWorks2012]
    TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2012.bak'
    WITH FORMAT, COMPRESSION, INIT, STATS = 15;