How to plug in today''s date for dump filename?

  • I am trying to run the following backup command but I would like to plug in the current month and day in the form of 'mmdd'. Here is the command:

    ***begin***

    BACKUP DATABASE testdb TO DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Daily\testdb[mmdd].bak'

    ***end***

    For example, if the backup command were scheduled to run today I would like the dump file to end up having the name of testdb0622.bak and tomorrow it would have the name of testdb0623.bak etc. How could I use GETDATE() and format it to only show the date in the form of mmdd and plug it into my script so that the dump file will be created with the correct name? Thanks!

    Ryan

  • use dynamic sql ex:

    declare @cmd as varchar(400)

    set @cmd = 'BACKUP DATABASE testdb TO DISK = ''E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Daily\testdb' +Right(convert(char(6),getdate(),12),4) +'.bak'''

    exec ( @cmd)

     


    * Noel

  • Finally one good utilisation of dynamic sql .

  • well, there are not many choices either

    [Edit:] Something is not right, I am getting multiple post everytime ??  


    * Noel

  • How many choices are there? 

    I wasn't born stupid - I had to study.

  • noeld, excellent. This works perfectly. I'm sure I'll get plenty of use out of this type of conversion in the future!

    Ryan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply