DPM 2007 and DPM 2010 – Remove Damaged Tape from Database

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.

CITATION: http://social.technet.microsoft.com/Forums/fr-FR/dpmtapebackuprecovery/thread/dede3c6c-aa37-440e-9205-25008d89059b

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 toBarcodeValue = @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.