Post reply

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

  • June 22, 2005 at 1:39 pm

    #66658

    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

  • June 22, 2005 at 2:02 pm

    #567667

    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

  • June 22, 2005 at 2:14 pm

    #567674

    Finally one good utilisation of dynamic sql .

  • June 22, 2005 at 2:38 pm

    #567691

    well, there are not many choices either

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


    * Noel

  • June 22, 2005 at 2:55 pm

    #567701

    How many choices are there? 

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

  • June 23, 2005 at 6:34 am

    #567861

    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