1

Trying to incorporate Date wise folder in the script. Following is the original code

  INSERT INTO @CurrentDirectories (ID, DirectoryPath, Mirror, DirectoryNumber, CreateCompleted, CleanupCompleted)
      SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN RIGHT(DirectoryPath,1) = '\' THEN '' ELSE '\' END + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar(max)),'\','$') END + '\' + @CurrentDatabaseNameFS + '\' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, Mirror, ROW_NUMBER() OVER (PARTITION BY Mirror ORDER BY ID ASC), 0, 0
      FROM @Directories
      ORDER BY ID ASC  

I added '\' + REPLACE(CONVERT(varchar(10), GETDATE(), 101), '/', '') +'\' + to the above code to have folders by date

INSERT INTO @CurrentDirectories (ID, DirectoryPath, Mirror, DirectoryNumber, CreateCompleted, CleanupCompleted)
      SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN RIGHT(DirectoryPath,1) = '\' THEN '' ELSE '\' END + '\' + REPLACE(CONVERT(varchar(10), GETDATE(), 101), '/', '') +'\' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar(max)),'\','$') END + '\' + @CurrentDatabaseNameFS + '\' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, Mirror, ROW_NUMBER() OVER (PARTITION BY Mirror ORDER BY ID ASC), 0, 0
      FROM @Directories
      ORDER BY ID ASC

Now after modifying the code with the above syntax , I could see the DateFolder gets created but somehow backup is not happening. See the error below

DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_create_subdir N'N:\Test\FullUserDBBackups\10152017\CLU-DBSQL-TEST$DB2012_TEST\AX_TEST\FULL_COPY_ONLY' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)

BACKUP DATABASE [] TO DISK = N'N:\Test\FullUserDBBackups\10152017\CLU-DBSQL-TEST$AX2012_PROD\AX_TEST\FULL_COPY_ONLYCLU-DBSQL-TESTAX2012_TEST_AX_TEST_FULL_COPY_ONLY_20171015_042734.bak' WITH CHECKSUM, COMPRESSION, COPY_ONLY

  • The error is on line 17. – mustaccio Oct 15 '17 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.