Category Archives: Uncategorized

Generate a Change Script to Move System Databases

I set this up to be modular and reusable, because I like reusing things.  This doesn’t actually move your system databases.  It just outputs a change script and a revert script.  You still have to run the generated script, then log on to your server and stop the services and move the files yourself.  In my example, I’m moving TempDB from C (ew) onto better storage.
declare @dbname sysname, @oldpath varchar(255), @newpath varchar(255)

set @dbname='tempdb' set @oldpath='C:\Program Files\Microsoft SQL Server\MSSQLSERVER\MSSQL\DATA' --no trailing slash set @newpath='H:\HappyStorage' --no trailing slash

/*  Generates your change script */ SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + replace(physical_name,@oldpath,@newpath) + ''');' FROM sys.master_files WHERE database_id = DB_ID(@dbname);

/* Generates a revert script */ SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + physical_name + ''');' FROM sys.master_files WHERE database_id = DB_ID(@dbname);

/* Tells you where the files are now */ SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID(@dbname); GO

Sample output would  include, in the top window:

alter database tempdb modify file (name=tempdev, filename = 'H:\HappyStorage\Data\tempdb.mdf'); alter database tempdb modify file (name=templog, filename = 'H:\HappyStorage\Log\templog.ldf');

Copy and paste that into a new window, and run it if you’re sure it’s right.

You can, of course, use this for MSDB or any other database you want to move.

Comments Off on Generate a Change Script to Move System Databases

Filed under Uncategorized

Pull the restore chain from MSDB

What files do I need to restore this database to a point in time?

You might know this off the top of your head, especially if you set up the backups or if you don’t have a lot of backups, but just in case you’re panicking, ask MSDB.  MSDB knows all… unless that data has been purged.  (Sorry.)

declare @dbname varchar(80),
@lastfull datetime,
@lastdiff datetime,
@fullback varchar(1024),
@diffback varchar(1024),
@RowsToProcess int,
@CurrentRow int,
@restorediff varchar(max),
@logback varchar(1024),
@restoreday datetime

set @dbname = ‘YourDB’
set @restoreday = ‘1/1/2016 8:05am’ –Make sure the time zone is your server’s time zone.

select @lastfull = max(backup_finish_date)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = ‘D’
WHERE d.database_id NOT IN (2, 3) and d.name=@dbname
and description like ‘Backup on%’
and backup_finish_date < @restoreday

SELECT    @fullback = m.physical_device_name
FROM         msdb.dbo.backupmediafamily AS m INNER JOIN
msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id
and b.type=’D’ and b.database_name=@dbname
AND b.backup_finish_date=@lastfull

print @fullback

SELECT @lastdiff = b.backup_finish_date FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’I’ and b.database_name=@dbname AND b.backup_finish_date>@lastfull AND b.backup_finish_date < @restoreday

If @@ROWCOUNT = 0
begin
set @lastdiff = @lastfull
end

select @diffback = m.physical_device_name FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’I’ and b.database_name=@dbname AND b.backup_finish_date=@lastdiff

print @diffback

CREATE TABLE #logs (RowID int not null primary key identity(1,1), logback nvarchar(255),finishdate datetime)
insert into #logs SELECT distinct m.physical_device_name, b.backup_finish_date FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’L’ and b.database_name=@dbname AND b.backup_finish_date>@lastdiff AND b.backup_finish_date<@restoreday order by b.backup_finish_date

SELECT @RowsToProcess=COUNT(logback) from #logs

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@logback=logback
FROM #logs
WHERE RowID=@CurrentRow
print @logback
END

drop table #logs

Comments Off on Pull the restore chain from MSDB

Filed under Uncategorized

Nagios Checks for Dell OpenManage Disk Health Written in PowerShell

I wrote these in PowerShell to run with nrpe/nsclient.  They query Dell OpenManage’s command line and return a Nagios-readable result.

(There are plugins available on Nagios Exchange, but they all seemed to be… more than I wanted.  I just wanted to know, “Is my RAID okay?”  Because whenever I’ve had an Event, I like to monitor for that event happening again.)

This checks the Physical Disks in the array.  If one or more disks reports anything other than OK, it alerts:

$status = 0; omreport storage pdisk controller=0 | Where-Object {$_ -match "^status"} | %{if($_ -notlike "*OK*"){$status=2}}

If ($status -eq 0) { Write-Host "OK: Physical Disks report OK" } else { Write-Host "CRITICAL: Check OpenManage" } exit$status

You might have to edit the scripts to check Virtual Disk health.  It could probably be made more elegant, but it suits my purposes.

This script checks the health of my C drive (vdisk 0):

omreport storage vdisk controller=0 vdisk=0 | ?{$_ -match "^status"} | %{$status=0}{if($_ -notlike "*OK*"){$status=2}}

If ($status -eq 0) { Write-Host "OK: Virtual Disk (OS) reports OK" } else { Write-Host "CRITICAL: Check OpenManage" } exit$status

This script checks the health of my data drive (E, vdisk 1):

omreport storage vdisk controller=0 vdisk=1 | ?{$_ -match "^status"} | %{$status=0}{if($_ -notlike "*OK*"){$status=2}}

If ($status -eq 0) { Write-Host "OK: Virtual Disk (data) reports OK" } else { Write-Host "CRITICAL: Check OpenManage" } exit$status

Running these involves adding lines like this to the external script section of nsclient.ini or equivalent:

check_physicaldisk = cmd /c echo scripts\\pdiskcheck.ps1; exit($lastexitcode) | powershell.exe -command - check_virtualdisk = cmd /c echo scripts\\vdiskcheck.ps1; exit($lastexitcode) | powershell.exe -command -

And lines like this to command.cfg in Nagios:

define command { command_name    check_physicaldisk command_line    /usr/lib/nagios/plugins/check_nrpe -H $HOSTADDRESS$ -c check_physicaldisk } define command { command_name    check_CRaid command_line    /usr/lib/nagios/plugins/check_nrpe -H $HOSTADDRESS$ -c check_virtualdisk }

Comments Off on Nagios Checks for Dell OpenManage Disk Health Written in PowerShell

Filed under Uncategorized

Squee!!!

As of yesterday, I’m a Microsoft MVP in Enterprise Security. 😀

Filed under Uncategorized

SQL Server Express Maintenance

Don’t you love looking at your old scripts?

I’ve rewritten all of our maintenance jobs, including the jobs in task manager that handle SBSMonitoring.  I’ve rid myself of Windows Internal Database, but was still using the script I wrote for it.  Not any more!

I decided to use and abuse Ola Hallengren’s scripts, so what I’m using for SBSMonitoring is:

Download and install CommandExecute.sql, DatabaseIntegrityCheck.sql, and IndexOptimize.sql. Then, create the following .sql file:

Declare @4wks datetime
set @4wks = DateAdd(hh, DateDiff(hh, 0, GETDATE()) – 672, 0)

EXECUTE [dbo].[IndexOptimize] @Databases = ‘ALL_DATABASES’, @OnlyModifiedStatistics = ‘Y’
EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ‘ALL_DATABASES’

exec msdb.dbo.sp_delete_backuphistory @oldest_date = @4wks

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME = ‘CommandLog’))
BEGIN
DELETE FROM [dbo].[CommandLog] WHERE DATEDIFF(dd,StartTime,GETDATE()) > 30
END

IF @@SERVERNAME = ‘MYSBSSERVER\SBSMONITORING’
BEGIN
EXECUTE [SBSMonitoring].[dbo].[CleanupDatabase]
END

This can be called with sqlcmd in Task Scheduler.  (“sqlcmd -E -SmyServer -i c:\path\to\scriptname.sql”)

I’ve also started using Microsoft’s script to reindex SUSDB.  I migrated that into SQL Server, so it’s an actual job, but SBS doesn’t support migrating the SBSMonitoring database out of the SBSMonitoring 2005 Express instance that’s installed by default, hence the scripty maintenance.

Comments Off on SQL Server Express Maintenance

Filed under Uncategorized

Fun with PXE, redux.

You may recall that I’m serving up not only Windows Deployment Services capture and deploy but also Acronis recovery media over PXE.

Well, now I’m also serving up Windows Defender Offline. When chosen from the PXE boot menu, it boots into Windows PE, automagically launches Defender, updates the definitions, and runs a full scan.  And then sits there and waits for you to see the results and hit reboot.

Here’s what I did.  This assumes you have Windows Deployment Services and the Windows Automated Installation Kit.

2. Run the tool to create an ISO (“to burn to CD/DVD later”).
3. Mount the ISO with Virtual CloneDrive (or similar product).
4. Fish boot.wim out of /sources on the virtual CD drive.
5. Create a directory to mount the wim into if one doesn’t already exist.
6. Mount boot.wim into that directory. Dism /Mount-Wim /WimFile:C:\temp\boot.wim /MountDir:C:\temp\wim /Index:1
7. If you have a directory full of drivers for your environment, you can pre-emptively add them with: Dism /Image:C:\temp\wim /Add-Driver /Driver:c:\drivers /Recurse /ForceUnsigned. (If not, cross your fingers and carry on with the next step.)
8. Copy mpam-fex64.exe (or similarly named file for x86)  into the top level of the wim from the top level of your virtual CD drive.
9. Save and unmount the wim.  Dism /Unmount-Wim /MountDir:C:\temp\wim /Commit

I’ve also rewritten a lot of my batch scripts to be PowerShell, but I think that’s a post for another day.

Comments Off on Fun with PXE, redux.

Filed under Uncategorized

Validation!

And how do I feel about that?

Filed under Uncategorized

Tea

We interrupt your regularly scheduled computer geekery to bring you tea.  I’ve been really getting into tea lately.  Also, caffeine abuse is a vital component of geek culture, and tea is relatively neglected (unless you’re steampunk).

So.

Loose leaf, my darlings.  It’s so much nicer.  It may look expensive, but it’s only a teaspoon (hence the name) per cup.  I have a relatively cheap glass teapot at the office.  Generally, I’ve found that the more intact the leaves are, the more likely I am to like it.  I hear this is because the more intact leaves have essential oils that evaporate if the leaves are crushed.  I will drink tea bags or Keurig tea, but given a choice I’d much rather have loose leaf.

I’ve found I don’t like drinking the exact same thing day after day, so I have a collection. I drink black tea in the mornings, green tea in the afternoons, and white tea in the evenings.  Except when I don’t, on impulse.

Black tea:  I’ve been doing this British style, more or less, with milk.  Well, with soymilk or soy creamer, either of which is probably heresy, but I’m okay with that.  Current favorites here include Twining’s Lady Grey (loose), Teavana Black Phoenix Pearls, and Republic of Tea’s 2oth Anniversary Celebration and Rose Petal Black Full-Leaf.

White tea:  I generally like flavored white teas.  Octavia Tea Company has strawberry and wild blueberry teas that I like a lot.  Rishi has rose petal white tea.  Silver needle by anyone (I think I’ve had Teavana, Rishi, and Octavia)  is also really nomalicious.

Oolong:  I’ve really only had Octavia Tea’s Amber Dragon, which was good.

Green tea:  I think that I prefer my green tea unflavored, although I do like Jasmine Pearls (which I’ve had from Octavia and Teavana).  Thus far I’m liking Gyokuro Imperial Green and Dragon Well.

I’m also eying fancier teapots, although a lot of those require you always drink the same kind (black, green, etc.) because the pot seasons to the variety.

Filed under Uncategorized

I should really call this post, “Everything I ever learned about job hunting I learned from writing,” even though that’s not really true.  However, there are, in fact, writing links in here.  (I’m a published short fiction author under a pseudonym.)

[Note:  I have a new job and am not looking at this time. I do, however, have friends and relatives who have requested resume help.]

The first thing you need to keep in mind when job hunting is that it’s not personal.  It’s not about you at all.  It’s about an employer who has a need to fill.  When I was job hunting, I kept this link in mind. A sample:

Anyway, as I was saying, it realio trulio honestly isn’t about you the writer per se. If you got rejected, it wasn’t because we think you’re an inadequate human being. We just don’t want to buy your book. To tell you the truth, chances are we didn’t even register your existence as a unique and individual human being. You know your heart and soul are stapled to that manuscript, but what we see are the words on the paper. And that’s as it should be, because when readers buy our books, the words on the paper are what they get.

This all becomes clearer if you think about it with your reader-mind instead of your author-mind. Authors with books are like mothers with infants: theirs is the center of the universe, uniquely wonderful, and will inevitably and infallibly be loved by all who make its acquaintance. This has its good aspects; books, like infants, need someone to unconditionally love them, and champion all their causes. On the other hand, it can be a form of blindness.

Your reader-mind has a different understanding of the whole book thing. Your reader-mind knows what it’s like to walk into a bookstore, or a Costco, or a Target, and confront a wire rack the size of your living-room wall, with slot after slot filled with books. At that moment, standing there in front of that rack, you don’t much care about encouraging new writers, or helping create a more diverse literary scene, or giving some author a chance to express herself. You want a book that will please you, and suit your needs, and do it right now. Dear reader, you are many things, but “gentle” isn’t one of them.

So, to extend the metaphor, pretend you’re hiring and have five hundred resumes on your desk and then look at yours.  (This may be easier if you’ve ever been on a hiring committee or sorted through resumes, but even if you haven’t, imagine.)  A recent article said that recruiters look at your resume for 6 seconds.  Make it easy for them, and put the stuff they mention specifically up near the top of the lists.  Don’t bury the shiny halfway down the page.  They might not get that far.

I also had a couple of people say that they weren’t sure what to do with the education portion.  Look, if you’re applying for jobs that say, “Associate’s degree or four years of relevant experience,” and you have five years of relevant experience and no degree, don’t put your high school under education or apologize for not having a degree (or, worse, claim you’re an MIT graduate).  Just don’t.  Leave that section off.  Opt for a compelling silence.  The resume police aren’t going to come get you if you leave off information they’re not even asking you to provide.  If, on the other hand, you have a degree in an unrelated subject, well, so do I.  Put it on there.  Your Master’s degree in Divinity from Harvard will give you and the interviewer something to talk about.

Cover letters:  This is a fiction cover letter, but I used this basic strategy for my job hunting cover letters.  Several years ago, someone asked me about cover letters, saying, in an aggrieved tone of voice and with an accompanying eyeroll, “So, I just rehash what my resume already says?”

No!  You do an eclectic celebration of the dance! You do Fosse Fosse Fosse Fosse Fosse! You do Martha Graham Martha Graham Martha Graham, or Twyla Twyla Twyla, or Michael Kidde Michael Kidde Michael Kidde Michael Kidde, or Madonna Madonna Madonna Madonna… but you keep it all inside.

Sorry.

No, you personalize your resume to the job listing, and your cover letter is a chance to explain why you are FREAKING AWESOME and perfect for the job by highlighting what’s already in your resume.  You know, in case they miss it by only looking at your resume for six seconds.

These are not a dry rehashes of your entire life up until this point.  They’re  marketing documents, and you’re the product.

By the way, you know they’re googling you, right?

And don’t forget the thank-you note.