locked
Can I rename the .bak file when using SQL Agent Job to schedule an automatic backup? RRS feed

  • Question

  • User747696199 posted

    Hi,

    We have a MVC3 ASP.NET application, that uses  SQL Server 2008 R2. We have used SQL Server Agent Job to schedule a daily job to backup the db at 2am everyday.

    However, the name of the .bak file is: OurDB.bak.

    Can we rename this file to be OurDB_11122015.bak, with today's date?

    How do we achieve this?

    If this cannot be done within SQL Server, is there a way to have a script renaming this file AFTER it is created?

    Your input is  appreciated.

    Claudia

    Thursday, November 12, 2015 11:21 AM

Answers

  • User747696199 posted

    Thanks - but I tried both answers, none is working.

    when I tried to run the job, it gibes error.

    It says "Execution of job failed"; but history log does not give any details.

    Update - I found this, and it worked:

    DECLARE @SQLStatement VARCHAR(2000) 
    SET @SQLStatement = 'E:\Test\AdventureWorks_' + CONVERT(nvarchar(30), GETDATE(), 110) +'.bak' 
    BACKUP DATABASE [AdventureWorks2012] TO  DISK = @SQLStatement

    Thanks,

    Claudia

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 19, 2015 11:01 PM

All replies

  • User77042963 posted

    You can append the date in your backup script.

    Here is a working sample for database test1:

    DECLARE @BackupFileName_test1 varchar(100)
    SELECT @BackupFileName_test1 = 'C:\backup\test1_' + Replace(CONVERT (VarChar(10), GetDate(), 110),'-','') + '.bak'
    
    BACKUP DATABASE test1 TO  DISK = @BackupFileName_test1
    WITH COPY_ONLY,INIT, COMPRESSION;

    Thursday, November 12, 2015 2:52 PM
  • User1992938117 posted

    As Limno Said,

    Use the below command

    DECLARE @MyFileName varchar(1000)
    SELECT @MyFileName = (SELECT 'C:\MyBackupLocation\MyDB_' + REPLACE(CONVERT(VARCHAR(500),GETDATE(),110),'-','') + '.bak') 
    BACKUP DATABASE [MyDB] TO  DISK = @MyFileName
    WITH COPY_ONLY,INIT, COMPRESSION;

    Thursday, November 12, 2015 11:30 PM
  • User747696199 posted

    Thanks - but I tried both answers, none is working.

    when I tried to run the job, it gibes error.

    It says "Execution of job failed"; but history log does not give any details.

    Update - I found this, and it worked:

    DECLARE @SQLStatement VARCHAR(2000) 
    SET @SQLStatement = 'E:\Test\AdventureWorks_' + CONVERT(nvarchar(30), GETDATE(), 110) +'.bak' 
    BACKUP DATABASE [AdventureWorks2012] TO  DISK = @SQLStatement

    Thanks,

    Claudia

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 19, 2015 11:01 PM