By Denise Crabtree, Senior Database Administrator Consultant at Taos
Finding the backup file location, time, how or who backed up a SQL Server database is answered with this query.
SQL Server keeps a backup history in the system database, msdb, for both native and third party backup utilities.
In addition the Server and Database names, the output provides information about the backup such as when it started & finished, the names and locations of the file(s), who ran it, how long it took, and the type of backup. In the above example, a full backup of master ran on three consecutive days. Third party utilities may use a GUID type address for the file location – knowing that a backup file exists is sometimes the first step in beginning a restore. This information can then be used to request the necessary files from a backup administrator. Here is a sample of a third party backup information returned by the query.
Finding the answer to the question, “Where are the backups?” is handy to have available. This is a query to keep.