Possible to Get List of Managed Backups?

SloopJohnBSloopJohnB Posts: 25 Bronze 3
edited May 30, 2012 3:45AM in SQL Virtual Restore 2
Hi -
I have purchased Virtual Restore for my reporting server. On this server, I have already set up over a dozen DBs which are managed via SQL Virtual Restore. Some of these DBs are long-term and some are ad-hoc (i.e., for quick testing of proposed updates, etc.).

Since (1) I have so many DBs and (2) I have problems with Virtual Restore holding on to backups even after a Virtual DB is deleted, I have taken to documenting which backup files are being used by which virtual DBs. Right now, this is in a simple manually-maintained Excel worksheet. I'm wondering if there is a way that this information can be gleaned out of the Virtual Restore utility itself, so that I can take the manual (human) element out of the equation.

Thanks in advance for your help.


  • Hi, I have made the same request to support, apparently there is already an issue reported: ref. SVR-256. Just email support and ask them to do a +1 on your behalf.

    Our disk will soon fill up unless we can solve this in an automated way!

  • Hi,

    In the meantime, before this information is available in the UI, you can use the following SQL script to get the information you are after, let us know if this helps:
    set nocount on
    declare @dbname sysname
    declare @sqlstmt varchar(1024)
    declare dbases cursor
    for select name from sys.databases where database_id > 4
    for read only
    print 'The following databases have been Virtually Restored:'
    open dbases
    fetch next from dbases into @dbname
    while @@fetch_status = 0
    	select @sqlstmt = 'IF (SELECT count(*) FROM {DATABASENAME}.sys.database_files where (RIGHT(physical_name,4) = ' + char(39) + 'vmdf' + char(39) + ') OR (RIGHT(physical_name,4) = ' + char(39) + 'vldf' + char(39) + ') OR (RIGHT(physical_name,4) = ' + char(39) + 'vndf' + char(39) + ')) > 0 PRINT ' + char(39) + '{DATABASENAME}' + char(39)
    	select @sqlstmt = REPLACE(@sqlstmt, '{DATABASENAME}', @dbname)
    	exec (@sqlstmt)
    	fetch next from dbases into @dbname
    close dbases
    deallocate dbases
    Jeffrey Aven
    Product Management - HyperBac Technologies
    Red Gate Software
  • Thank you for the sql, but I really need a way to find which Hyperbac zip-files are being used.

    The reason is that we have automated backup/restore routines, so we can not rely on people making notes of which files are being used and which are not used anymore.
    So, I need to able to get this information form tsql/bat/shellscript or some other way, so that I can write some code that automatically removes unused zip files.
Sign In or Register to comment.