get Actual backup file name from the sql backup path

• Siten0308

  SSCarpal Tunnel

  Points: 4409

  More actions

  February 12, 2021 at 4:41 pm

  Go to Answer

  #3844284

  Hey everyone,

  sorry, i am trying to get the actual file name accurately from the path, what i currently have is:

  E:\Backup\Server1\Database1\Database1_131983519013.bak

  and want to get just:

  Database1_131983519013.bak

  trying to use trim or left etc. but finding it difficult as some other database names might be longer or shorter, so its random, any idea how I can get just the name?

   

  thanks in advance

• Kristen-173977

  SSCrazy Eights

  Points: 8687

  More actions

  February 12, 2021 at 4:52 pm

  Answer

  #3844285

  This perhaps?

  DECLARE @MyString varchar(255)='E:\Backup\Server1\Database1\Database1_131983519013.bak'
  
  SELECT RIGHT(@MyString, CHARINDEX('\', REVERSE(@MyString)) - 1)
  

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  February 14, 2021 at 12:07 pm

  #3844866

  If you know the folder path in advance, you can simply remove it to get the file name:

  DECLARE @BackupFilePath VARCHAR(300) = 'E:\Backup\Server1\Database1\Database1_131983519013.bak';
  
  SELECT
      @BackupFilePath
  ,   FileName = REPLACE(@BackupFilePath, 'E:\Backup\Server1\Database1\', '');

  ---

• Siten0308

  SSCarpal Tunnel

  Points: 4409

  More actions

  February 16, 2021 at 11:09 pm

  #3845882

  Thank you sir, worked perfectly