substring or other method

• Mad-Dog

  SSCertifiable

  Points: 6117

  More actions

  January 13, 2009 at 2:17 am

  #155117

  Hi,

  i want to insert into a variable the backup path of a specific database

  without the backup name only the path (D:\backup\databasename\).

  i use this syntax to view the whole backup path but i don't know how to remove the backup name from the path.

  the path and backup name are changes from one server to another but the backup file is always start with databasename and end with .bak

  can you help me with the current syntax?

  THX

  select top 1 bset.database_name ,bfamily.physical_device_name,bset.backup_finish_date from

  backupmediafamily bfamily,backupset bset

  where bset.media_set_id = bfamily.media_set_id

  and bset.database_name = 'databasename'

  order by bset.backup_finish_date desc

• NitinPatel31

  Hall of Fame

  Points: 3721

  More actions

  January 13, 2009 at 2:39 am

  #925721

  Try this

  DECLARE @s-2 VARCHAR(100)

  SET @s-2 = 'D:\backup\databasename\Backupname.bak'

  SELECT SUBSTRING(@s, LEN(@s) - CHARINDEX('\', REVERSE(@s)) + 2, LEN(@s))

  Regards,
  Nitin

• Mad-Dog

  SSCertifiable

  Points: 6117

  More actions

  January 13, 2009 at 2:45 am

  #925724

  thx.

  i need the path,not the filename.

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  January 13, 2009 at 2:58 am

  #925730

  This will work

  DECLARE @file VARCHAR(100)

  SET @file = 'D:\backup\databasename\Backupname.bak'

  SELECT LEFT(@file, LEN(@file) - CHARINDEX('\',REVERSE(@file)))

  [font="Verdana"]Markus Bohse[/font]

• NitinPatel31

  Hall of Fame

  Points: 3721

  More actions

  January 13, 2009 at 8:20 am

  #925921

  Mad-Dog (1/13/2009)

  ---

  thx.

  i need the path,not the filename.

  This script will give you path

  DECLARE @s-2 VARCHAR(100)

  SET @s-2 = 'D:\backup\databasename\Backupname.bak'

  SELECT SUBSTRING(@s, 1, LEN(@s) - CHARINDEX('\', REVERSE(@s)))

  Regards,
  Nitin