How can we improve SQL Backup?

Feature request: Store the "CopyTo" location when recording a backup

In my environment, storing local copies of backups simply isn't an option - there isn't disk space enough even to write a single night's full backup set! So all my backups must be written directly to network storage (yes, I know that's not ideal, but I'm stuck with it for the moment). However, management has approved hardware upgrades for this year, and the replacement servers I've got coming with have at least enough disk to capture a night's backup prior to copy to NAS for long term retention. Here's the problem...

I've built a tool for my developers and non-tech users that allows them to restore production backups onto diagnostic databases. In order to present a list of available restore points I have to be able to query which backups are still available. In addition, I have to be able to identify which full backup serves as the base for each differential backup.

Unfortunately, msdb contains only the location to which the initial backup was actually written - it doesn't know that RedGate offers a "copy to network location" option. If the backup is first written to local storage and then copied to network storage, no record of the "copied to" location is kept. I'd like to be able to write today's backups to local disk and then transfer them immediately to network storage, with the option to query the backup records to find and retrieve the network copies on demand.

1 vote
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Doug tucker shared this idea  ·   ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Yeoh Ray Mond commented  · 

        SQL Backup stores details of its backup and restore processes in a SQL Server Compact database on the server. You can access this database using SQL Server Management Studio, or via a SQL Backup stored procedure that acts as a proxy to access the database. E.g.

        EXEC master..sqbdata 'SELECT * FROM backuphistory'

        will return all rows from the 'backuphistory' table in that database.

        2 tables that would help in retrieveing details of the copied backup files are the 'backuphistory' and 'backupfiles' table. In the 'backupfiles' table, there is a column named 'file_type'. A value of 'P' indicates the primary backup file, 'C' indicates a copied file, and 'M' indicates a moved file. The 'backup_id' value links to the 'id' value in the 'backuphistory' table. The 'backup_set_uuid' value in 'backuphistory' in turn links to the 'backup_set_uuid' value in the msdb..backupset table.

        So to retrieve details of backup files created by SQL Backup given a specific backup_set_uuid value, you might run something like this:

        EXEC sqbdata 'SELECT a.name, a.file_type FROM backupfiles a INNER JOIN backuphistory b ON a.backup_id = b.id WHERE b.backup_set_uuid = ''{47CA7E29-9C76-4281-841C-C6D79446B86B}'''

      Feedback and Knowledge Base