Possible to Get List of Managed Backups?

SQL Virtual Restore: Rapidly mount live, fully functional databases direct from backups

Moderators: eddie davis, Colin Millerchip, fionag

Possible to Get List of Managed Backups?

Postby SloopJohnB » Tue May 22, 2012 6:39 pm

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.
SloopJohnB
 
Posts: 18
Joined: Sat Jul 22, 2006 1:19 am

Postby fgsimon » Wed May 23, 2012 9:49 am

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!

/Simon
fgsimon
 
Posts: 18
Joined: Mon May 21, 2012 1:00 pm

Postby javen » Wed May 30, 2012 1:43 am

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:

Code: Select all
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
begin
   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
end

close dbases
deallocate dbases


Jeffrey Aven
Product Management - HyperBac Technologies
Red Gate Software
javen
 
Posts: 50
Joined: Tue May 25, 2010 12:10 am

Postby fgsimon » Wed May 30, 2012 8:45 am

Thank you for the sql, but I really need a way to find which Hyperbac zip-files are being used.
/Simon

PS
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.
fgsimon
 
Posts: 18
Joined: Mon May 21, 2012 1:00 pm


Return to SQL Virtual Restore 2

Who is online

Users browsing this forum: No registered users and 0 guests