| Author |
Message |
SloopJohnB
Joined: 22 Jul 2006
Posts: 17
|
 Posted: Tue May 22, 2012 6:39 pm Post subject: Possible to Get List of Managed Backups? |
 |
|
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. |
|
| Back to top |
|
 |
fgsimon
Joined: 21 May 2012
Posts: 18
|
| Posted: Wed May 23, 2012 9:49 am Post subject: |
|
|
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 |
|
| Back to top |
|
|
javen
Joined: 25 May 2010
Posts: 50
|
| Posted: Wed May 30, 2012 1:43 am Post subject: |
|
|
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: |
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 |
|
| Back to top |
|
|
fgsimon
Joined: 21 May 2012
Posts: 18
|
| Posted: Wed May 30, 2012 8:45 am Post subject: |
|
|
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. |
|
| Back to top |
|
|
|
|
All times are GMT + 1 Hour
|
| Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
