title: Is it possible to find out the last backup location used for a transaction log file in SQL 2005 or higher
body: <p>We have a product which uses a database and whenever a major release is done one of the upgrade scripts used on the database calls one of our stored procedures whose job is to drop and then recreate the fulltext index thus ensure any changes (i.e. new columns that are indexed) in the new release get installed.</p>
  
  <p>The problem is that if our client has set up the database to be full recovery mode then this will fail at the point at which it tries to recreate the fulltext index.</p>
  
  <p>To ensure the upgrade instructions are as simple as possible I don't want the IT guy doing it to have to do anything other than run the set of scripts according to there filenames, (i.e. run 001 - xxx.sql, then run 002 xxx.sql etc...).</p>
  
  <p>So the idea I thought to try was for the SP that drops/recreates to do the backup of the transaction log if the database is set to full recovery mode for them by backing up to the same location as the last transaction log backup was done. The problem is how do you find out the last location?</p>
  
  <p>I've searched and found scripts that indicate the use of sys.sysdatabases and msdb..backupset but those tables don't seem to have the information I need.</p>
  
  <p>Any ideas? Is it even possible?</p>