Microsoft DPM‎ > ‎

DPM 2007 and DPM 2010 - Remove Damaged Tape from Database

posted 15 May 2013, 01:26 by Tristan Self   [ updated 29 May 2013, 00:36 ]
Tapes are basically sticky tape and rust, therefore they are liable to break especially your weekly rotation ones, just because they get wear and tear. When this happens and the tape is no longer able to be used, we need to remove it from DPM to stop it appearing the in the tape request reports. With DPM, there isn't anyway to remove the tape using the GUI so you need to run a SQL script to get rid of the tape and stop it asking for something you can never put into the tape drive.
 
1. First get hold of the tape label name, for example: 000013L4
 
2. Next backup your DPMDB database, this is a must, if this script corrupts it you want to be able to restore. You can do this by:
    2.1 DPMBACKUP -db (The database will be saved in the C:\Program Files\Microsoft DPM\DPM\Volumes\ShadowCopy\Database Backups folder.)
    2.2 Microsoft SQL Management Studio - Start off a (copy) backup of the DB and store it somewhere safe.
 
3. Now open the Microsoft SQL Management Studio, open a "new query", select the DPMDB database as the target for the operation.
 
4. Now paste into the window the following:
---------- START COPY HERE -------------
 
-- overdue tapes
 -- for clarity, set up the parameter as a variable

 declare @paramTapeLabel as nvarchar(256)
 set @paramTapeLabel = 'SAMPLE_TAPE_LABEL_NAME'
 
-- keys
 declare @vMediaId as guid
 declare @vGlobalMediaId as guid
 
-- if the delete gives trouble, add keyset after cursor
 declare cur_label cursor
 for select MediaId, GlobalMediaId
 from tbl_MM_Media
 where label = @paramTapeLabel;
open cur_label
 while (0 = 0)
 begin
 fetch next from cur_label into @vMediaId, @vGlobalMediaId

 -- test for being done
 if @@fetch_status <> 0 break;
print 'Deleting MediaId = ' + cast(@vMediaId as varchar(36))

 -- do a set of deletes atomically
 begin transaction;
 delete from tbl_MM_TapeArchiveMedia
   where MediaId = @vMediaId;
delete from tbl_MM_MediaMap
   where MediaId = @vMediaId;
delete from tbl_MM_ArchiveMedia
   where MediaId = @vMediaId;
delete from tbl_MM_Global_ArchiveMedia
   where MediaId = @vGlobalMediaId;
delete from tbl_MM_Global_Media
   where MediaId = @vGlobalMediaId;
delete from tbl_MM_Media
   where current of cur_label;
commit transaction;
end
 close cur_label
 deallocate cur_label
 
-------------- END COPY HERE ----------------------
 
5. Alter the line @paramtapelabel where the N'SAMPLE_TAPE_LABEL_NAME' should be replaced with: '000013L4' (that's right you leave in the single quotes, or it won't work.)
 
6. Now execute the script, assuming it worked without error, that's it the tape has been deleted from the database.
 
7. You should find that the next tape report won't contain the faulty tape as it no longer knows about it and the job will use a new one instead.
 
I've repeated this post on my site as its too good to lose to the Interwebs.
 
UPDATE for Microsoft DPM 2010
 
If you are using Microsoft DPM 2010, you need to adjust the code slightly, because the name has been changed of one of the columns where it has the Label = @paramTapeLabel; you need to change this to BarcodeValue = @paramTapeLabel; instead. Now when you run this script you should see 5 or 6 results saying:
 

Deleting MediaId = 5DCFFDBC-29E9-4572-B1C1-BE1A9A10A237

(1 row(s) affected)

(5 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

 
-- overdue tapes
 -- for clarity, set up the parameter as a variable
 declare @paramTapeLabel as nvarchar(256)
 set @paramTapeLabel = '000013L4'
-- keys
 declare @vMediaId as guid
 declare @vGlobalMediaId as guid
-- if the delete gives trouble, add keyset after cursor
 declare cur_label cursor
 for select MediaId, GlobalMediaId
 from tbl_MM_Media
 where BarcodeValue = @paramTapeLabel;
open cur_label
 while (0 = 0)
 begin
 fetch next from cur_label into @vMediaId, @vGlobalMediaId
 -- test for being done
 if @@fetch_status <> 0 break;
print 'Deleting MediaId = ' + cast(@vMediaId as varchar(36))
 -- do a set of deletes atomically
 begin transaction;
 delete from tbl_MM_TapeArchiveMedia
   where MediaId = @vMediaId;
delete from tbl_MM_MediaMap
   where MediaId = @vMediaId;
delete from tbl_MM_ArchiveMedia
   where MediaId = @vMediaId;
delete from tbl_MM_Global_ArchiveMedia
   where MediaId = @vGlobalMediaId;
delete from tbl_MM_Global_Media
   where MediaId = @vGlobalMediaId;
delete from tbl_MM_Media
   where current of cur_label;
commit transaction;
end
 close cur_label
 deallocate cur_label
 
This seemed to cure the problem for me.
Comments