Tags: 91database, backup, backups, commandbackup, database, date, differential, disk, filename, following, insert, microsoft, mysql, oracle, scheduledjob, server, sql, via

Insert date into filename during backup

On Database » Microsoft SQL Server

3,626 words with 4 Comments; publish: Sun, 18 May 2008 00:29:00 GMT; (25094.24, « »)

I'm currently doing differential backups via the following scheduled

job command:

BACKUP DATABASE [DATABASE] TO DISK = N'c:\backup\DATABASE-

Differential' WITH INIT , NOUNLOAD , NAME = N'DATABASE differential

backup', NOSKIP , DIFFERENTIAL, STATS = 10, NOFORMAT

However, I'd like to do the backups several times per day (for

instance with transaction logs), but my current command overwrites the

previous backup. So, is there a way I can easily insert the current

date/time into the filename?

Thanks!

All Comments

Leave a comment...

  • 4 Comments
    • Tweak the CONVERT function (3:rd parameter) to your liking:

      DECLARE .sqlserver.todaysummary.com.sql varchar(2000)

      SET .sqlserver.todaysummary.com.sql = 'BACKUP DATABASE pubs TO DISK = N''c:\pubs'

      SET .sqlserver.todaysummary.com.sql = .sqlserver.todaysummary.com.sql + REPLACE(CONVERT(varchar(25), CURRENT_TIMESTAMP, 120), ':',

      ' ')

      SET .sqlserver.todaysummary.com.sql = .sqlserver.todaysummary.com.sql + '.bak'' WITH INIT , NOUNLOAD'

      PRINT .sqlserver.todaysummary.com.sql

      EXEC(.sqlserver.todaysummary.com.sql)

      Tibor Karaszi, SQL Server MVP

      http://www.karaszi.com/sqlserver/default.asp

      http://www.solidqualitylearning.com/

      <jevansts.sqlserver.todaysummary.com.yahoo.com> wrote in message news:1170180393.729166.276980.sqlserver.todaysummary.com.p10g2000cwp.googlegroups

      .com...

      > I'm currently doing differential backups via the following scheduled

      > job command:

      > BACKUP DATABASE [DATABASE] TO DISK = N'c:\backup\DATABASE-

      > Differential' WITH INIT , NOUNLOAD , NAME = N'DATABASE differential

      > backup', NOSKIP , DIFFERENTIAL, STATS = 10, NOFORMAT

      > However, I'd like to do the backups several times per day (for

      > instance with transaction logs), but my current command overwrites the

      > previous backup. So, is there a way I can easily insert the current

      > date/time into the filename?

      > Thanks!

      >

      #1; Sun, 18 May 2008 00:31:00 GMT
    • Tibor,

      Thank you so much for the CURRENT_TIMESTAMP script.

      One questions, have you been able to find a way to delete those

      differential.bak files that are passed there retention date? The only

      issue I see in this is that all your .bak files are kept and not

      overwritten.

      Thank you,

      Jose L. Bautista

      #2; Sun, 18 May 2008 00:32:00 GMT
    • Check out for instance:

      http://realsqlguy.blogspot.com/2007...se-backups.html

      Tibor Karaszi, SQL Server MVP

      http://www.karaszi.com/sqlserver/default.asp

      http://sqlblog.com/blogs/tibor_karaszi

      <jose.l.bautista.sqlserver.todaysummary.com.gmail.com> wrote in message

      news:1174084102.542501.236560.sqlserver.todaysummary.com.d57g2000hsg.googlegroups.com...

      > Tibor,

      > Thank you so much for the CURRENT_TIMESTAMP script.

      > One questions, have you been able to find a way to delete those

      > differential.bak files that are passed there retention date? The only

      > issue I see in this is that all your .bak files are kept and not

      > overwritten.

      > --

      > Thank you,

      > Jose L. Bautista

      >

      #3; Sun, 18 May 2008 00:33:00 GMT
    • Tibor,

      Good morning. I am having huge issues with the link that you gave me.

      Maybe and I know it is, because Im still trying to get grasp of T-SQL,

      I am not able to run script you gave me successfully. Email me if you

      can.

      Thanks,

      Jose

      #4; Sun, 18 May 2008 00:34:00 GMT