Execute the following Microsoft SQL Server script to demonstrate the renaming and archiving of a file. Such a file may contain data feed which arrives everyday under the same name. After processing, it can be renamed with the datestamp in the filename and moved into the archive folder. File existence check is performed first. The example below shows the datestamping of a backup file.

USE AdventureWorks;

DECLARE  @SYSCommand NVARCHAR(512)

DECLARE  @DateSuffix CHAR(8)

SELECT @DateSuffix = convert(CHAR,GETDATE(),112)

CREATE TABLE #IsFileExists (

  fileexists SMALLINT,

  direxist   SMALLINT,

  parentdir  SMALLINT)

SELECT @SYSCommand = '\\114.212.116.112\D$\data\backup\Accounting\Accounting.BAK ' +

'Accounting' + '.BAK'

PRINT @SYSCommand

INSERT INTO #IsFileExists

EXEC MASTER.dbo.xp_fileexist

  @SYSCommand

IF EXISTS (SELECT 1

           FROM   #IsFileExists

           WHERE  fileexists = 1)

  BEGIN

    SELECT @SYSCommand = 'ren \\114.212.116.112\D$\data\backup\Accounting\Accounting.BAK ' +

    'Accounting' + @DateSuffix + '.BAK'

    PRINT @SYSCommand

    EXEC MASTER.dbo.xp_cmdshell

      @SYSCommand

    SELECT @SYSCommand = 'move \\114.212.116.112\D$\data\backup\Accounting\' + 'Accounting' +

    @DateSuffix + '.BAK' + ' ' + '\\114.212.116.112\D$\data\backup\Accounting\archive'

    PRINT @SYSCommand

    EXEC MASTER.dbo.xp_cmdshell

      @SYSCommand

  END

GO

------------

Related article:

How to rename a backup file with date suffix?