Automating SQL Express Backups for Fun and Profit

Here's how to back up SQL and SQL Express databases automatically.

Phase One:

1. Create two folders on the C Drive: C:\DB_Backup and C:\ScheduledTasks. DB_Backup is the target for the backup job, and ScheduledTasks holds the SQL script that will be triggered via a scheduled task.
2. Install an appropriate version of SQL Management Studio Express (SSMS).
3. In SSMS, connect to the instance by using <ServerName>/<InstanceName>. An easy way to find the instance name is to open the services console (Start, Run, services.msc, enter) and look for your SQL service. The instance name will be within parentheses next to it.
4. Now, right click on the database and choose Tasks-->Backup
5. In the destination area, choose to back up to disk, and click the 'Add' button.
6. Navigate to the DB_Backup folder that you created, and make up a file name with a .bak extension.
7. Click on the 'Options' page on the left, and select 'Overwrite all existing backup sets'
8. Place a check mark next to "Verify backup when finished'
9. Click the down arrow next to 'Script' at the top of the window, and choose 'Script Action to New Query Window' -- This is an excellent way to learn SQL, by the way. You can do this from nearly everywhere in SSMS.
10. Now, press the cancel button on the backup window (we're not going to back it up now)

You should now see the SQL code for the backup in a query window. Here's an example:

BACKUP DATABASE [DatabaseName] TO  DISK = N'C:\temp\DatabaseName.bak' WITH NOFORMAT, INIT,  NAME = N'etl-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DatabaseName' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DatabaseName' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''etl'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'C:\temp\DatabaseName.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO


Phase Two:

1. Click File-->Save SQLQuery1.sql As... and save the sql script file to C:\ScheduledTasks
2. You can now close SSMS
3. Open Task Scheduler
4. Right-click 'Task Scheduler Library' on the left, and choose 'Create Task'
5. Name it "<Server> SQL Express Backup"
6. Choose an appropriate user to run as
7. Select Run whether the user is logged on or not
8. Select Run with highest priveledges
9. Choose highest "configure for" level available
10. Select the Triggers tab
11. New....
12. Set the scheduled time.
13. Select the Actions tab
14. New...
15. In the program/script section, put the path to sqlcmd.exe. Mine is "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SqlCmd.exe" (if the path includes spaces, use quotes). Yours might be different depending on your version of SQL.
16. In the arguments field, put the following: -S <ServerName>\<InstanceName> -i "C:\Scheduled Tasks\<ServerName>-<DBName>.sql"
17. Click OK
18. Once your actions are completed, click on the settings tab
19. Change the "Stop the task if it runs longer than" value to 1 hour. You can change this depending on how long an actual backup takes for your database, of course.
20. Click OK to finish creating the task
21. Enter the credentials for the user account that is to run the task
22. Run the scheduled task to test it, and check C:\DB_Backup for the .bak file(s)

And that's it! Setting this up on our SQL Express databases saved us a ton of money....

Heads-Up Display for Network Monitoring (Combining Multiple Websites into One)

When I started my new job, one of the first things I did was to purchase and set up Paessler's PRTG network monitoring software. PRTG is a great piece of software for the money. It's VERY worth it to have network monitoring up and running. You get notifications when something goes down and access to the monitoring software via your cellphone (Android or iPhone). On top of that, it can track historical usage data, which I find most helpful dealing with disk space It's nice to be able to know a month ahead of time that you're going to run out of space on a drive and that you need to do something about it.

Another thing I love about having an at-a-glance display of my servers and applications is that when I'm done patching and rebooting tons of servers (I have 60), you can look at a pretty picture and know immediately whether everything's working correctly or not. We have a 50-inch TV in our hallway connected to a computer that is used to display various pretty pictures from our monitoring websites, and I had to learn a little bit (more) about HTML to make it work properly. Here's my HTML code:


<html>
<head>
<title>This is how we do it......</title>
</head>
<frameset cols="65%,35%">
<frameset rows="65%,35%">
<frame src="https://MyPRTGServer/alarms.html" />
    <frame src="http://MySpiceworksServer/tickets/list/open_tickets#" />
</frameset>
<frameset>
<frame src="https://MyPRTGServer/Sunburst" />
</frameset>
</frameset>
</html>

The result:

I put a link on the desktop, and after I open the webpage the individual components load. I then press F11 to make it full screen, removing the dozen toolbars that are somehow installed (I kid). One caveat to this is that if you are opening HTTPS websites within your frames, and those pages have a self-signed cert, you will need to open them individually first so that the browser knows that you trust them (I understand the risk!). Only after doing that will your browser allow you to open those pages within an iframe.

So, the webpage. You have two columns. The first column is set up on line 5, and is 65% of the screen. This real estate is then split up 65/35 to show our PRTG alarms on the top, and our Spiceworks helpdesk tickets at the bottom. The second column shows the PRTG Sunburst. The trick with using the frameset tag this way is that you declare the columns, then declare the rows inside of that column, then declare your next column, etc. When I come upstairs in the morning, after I check the server room, I walk past this display and it's the first thing I see. If there's an error, it will be red, pink signifies errors that someone's already acknowledged, and if it's all green, then I go make myself some coffee!

I recommend that every sysadmin learns a little bit of HTML. You don't need to mess with CSS style sheets and Java code (though it might help you more, depending on how "dev" your role is), but it's nice to quickly whip up a page of links, or edit an IIS error page on the fly. One great resources I've found (and use) for HTML coding (by the by, I think programmers in general despise when you call writing HTML 'coding') is the W3Schools website found here. I recommend using notepad++ for any text editing, including HTML and Powershell Scripts, by the way.

WSUS Logs and a GREAT Performance Monitor tool I found

Some finger pointing went on at work, so I thought it might be wise to figure out where WSUS kept its logs. Nothing's going to come of it, but it just shed light on something that I didn't know about. Who knows? Someday I might need to know where they are! The complete list is here on technet, but the part that I'm most interested in is, "Who approved which WSUS updates on what date?" For that, you need to look at the file %ProgramFiles%\Update Services\Logfiles\Change.log.

I also was pouring through 4sysop.com's master list of "free" sysadmin tools. As a cultural aside, it's unfortunate that we need to put definitive words like 'free' in quotes so that people don't take them literally. If the word you're using isn't accurate, pick a different word; we have plenty to choose from! I digress. There are some real free (literally) gems in there that I use quite often (Spiceworks, Notepad++, EMCO MAC Address Scanner, and WinDirStat to name a few of my favorites). There's also a lot of things that are decidedly NOT free, or there is a free version, but it's crippled enough to make it nigh unusable. Today while going through the list I found an app called ControlUp. This is free up to a point, but I have a relatively small environment, and even fewer servers on which I need to monitor performance. This little app has definitely earned a place on my monitoring screen.

Starting Processes or Commands Within Powershell

It's been a busy couple of days. Saturday night I spent at work doing this month's Windows patches. I'm tasked with trying to put together a patching procedure for about 70 servers that can be shared among multiple admins so that we don't step on each other's toes. Our SAN doesn't have a whole lot of throughput, so when multiple VMs are patched simultaneously it tends to slow everything down. It's almost easier to do it myself, really, but in the long run my family (and my sleep schedule) will thank me for spreading the load.

New Powershell command of the week: Start-Process
This is the command I ended up using to automate my Veeam Backup & Recovery jobs. One example for usage is:
start-process "C:\Robocopy.exe" -ArgumentList '"j:\Backup Exec\Backups "Z:\Backup Exec\Backups" /MIR /NP /LOG:C:\RobocopyBE.log'

Notice that my argument is blocked within single quotes, and then the paths, which include spaces, are blocked in double quotes, as is customary with this command. The single quotes tell Powershell to parse the text in between them as is. Here is a really cool blog post about the different ways that Powershell can run commands. While start-process is itself not addressed, usage of Invoke-Command and Invoke-Expression are. I like that there are multiple ways to accomplish things in Powershell. Sometimes I end up beating my head against the wall trying to get a command to work a certain way, and if I don't get too stubborn, I find that I can usually back up, find a different methos to achieve my ends, and then move on more quickly.

Screen Real Estate Upgrade

So I got a new HP Elitebook 8460p laptop at work, and it's very nice. Having an SSD makes SO much of a difference. Therefore, I had to reinstall everything. What a pain. While I was waiting for the 100+ Windows Updates to install, I decided to look around the web and see if I could find any new and useful tools. I do a lot of multitasking (this laptop has 16GB of RAM and I have a docking station running dual 22" monitors), so screen real estate is quite important to me. Here are 3 really cool apps I have found:

qttabbar - This gives you tabs in Windows Explorer. Did I mention that it's free?
Office Tab - This gives you tabs in Excel, Word, and Powerpoint! There's a more expensive version that does Project and Visio, but I struggle so with Visio that I'm of a single purpose when I'm in that app. It costs 30 bucks, and I'm still in eval mode, but so far so good. I really only need the tabs for Excel.

And the prize for most amazing app of this decade (I'm really excited about this, can't you tell):
Actual Multiple Monitors

I can't say enough great things about this software. I was reading about other apps that fit this niche and everyone had one complaint or another about them. I downloaded this and was blown away. I can pin stuff to either screen, and the items on the screen only show up in that screen's taskbar. I can have seperate toolbars for each screen's taskbar. It even allows my desktop to be continuous horizontally and vertically! Seriously, if I take my mouse off the left side of my second monitor, it appears on the right. How cool is that? I used it for 5 minutes (and most of that was configuration) and immediately plunked down $30 for it. It's worth its weight in gold as far as I'm concerned.

I'm also playing around with Launchy, which is a ... er.. launcher that you just type things into. This seems a lot like me just hitting the start orb and typing, but I'm checking it out. I know it's pretty popular, so it must be useful in some other capacity. The less I HAVE to use my mouse, the better.

In Powershell land, I discovered a very nice function called New-PSDriveHere that allows you to easily create PSDrives. Mad props to The Lonely Administrator.

Here's a way to "Open Powershell Prompt Here" from Windows Explorer, like we used to do with DOS back in the day.

Powershell - Adding A Trusted Server

So I'm going through and automating my new infrastructure. I don't have the money for a log analysis tool, so I've created a tidy little script that will run on each of my servers that will gather error and warning events from the system and application logs over the past day and email them to me.

#Get yesterday's date and set some headers for the email I'll create later.

$Date = ((get-date).adddays(-1))
$SystemHeader = "`r`n+++   System Log Errors and Warnings    +++"
$ApplicationHeader = "`r`n+++ Applications Log Errors and Warnings  +++"
$DelimiterLog = "`r`n++++++++++++++++++++++++++++++++++++++++++++"
$Newline = "`r`n"

#Get the hostname
$Hostname = ($env:computername)

#Get Error/Warning events from the System log
$System = (get-eventlog system -after $Date -EntryType Error,Warning | select Entrytype, Source, EventID, Message | ft -autosize)

#Get Error/Warning events from the Application log
$Application = (get-eventlog application -after $Date -EntryType Error,Warning | select Entrytype, Source, EventID, Message | ft -autosize)

#Craft and send the email
$Body = $DelimiterLog
$Body += $SystemHeader
$body += $DelimiterLog
$body += ($System | Out-string)
$body += $Newline
$body += $Newline
$body += $DelimiterLog
$body += $ApplicationHeader
$body += $DelimiterLog
$body += ($Application | Out-string)
Send-Mailmessage -from "administrator@foo.com" -to "me@foo.com" -subject "Log Errors and Warnings from $Hostname" -smtpserver mailservername -body $body

I put the script on my central "management" server that performs all of my scheduled tasks. Everything ran fine when I tested the script, but most of my scheduled tasks failed. I remoted (that should be a verb in the dictionary at this point, methinks) into one of the servers that didn't run the script correctly, and lo and behold, when I ran the script from the local prompt I got a weird prompt:

Security Warning Run only scripts that you trust. While scripts from the Internet can be useful, this script can potentially harm your computer. Do you want to run \server\scripts\my.ps1? [D] Do not run [R] Run once [S] Suspend [?] Help (default is "D"):

This won't do!

After hunting around on the web I found out that the server you run the script from needs to be a trusted site in your Internet Settings. The easiest way I found to accomplish this was pushing out the following registry key via group policy:

Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\EscDomains\<servername>

Now, create a REG_DWORD named *, and set the data to 2 (decimal)

Because I wanted to trust our DFS infrastructure, the servername key was our root DFS namespace server.

And voila! No more prompts!

Calling Veeam jobs via a Powershell script

So I've been playing with using Veeam via Powershell. Well, I've only tried one thing, but I have it working now. I needed a Powershell script that would run different actions for different days. This Powershell script would be called at the end of my Veeam Daily backup job which starts every day at 5:30. I'm doing reverse incremental backups, so the end time of my job can fluctuate, and I needed something that would take a diffferent action based on the day of the week.
First, I created a scheduled task to run a simple Powershell script that wrote the day of the week to a text file, like so:

VeeamDayOfWeek.ps1
$DayOfWeek = ((get-date).dayofweek)
$DayOfWeek | out-string | out-file c:\temp\VeeamDayOfWeek.txt

This job is scheduled to run every day at 5pm. My backup job starts at 5:30. When my backup job completes, it then runs powershell.exe -File c:\PS\VeeamJobEnd.ps1.

VeeamJobEnd.ps1
Add-PsSnapIn VeeamPSSnapIn
$DayOfWeek = (Get-content c:\temp\VeeamDay.txt)
If ( ($DayOfWeek -like "*Monday*") -or ($DayOfWeek -like "*Tuesday*") -or ($DayOfWeek -like "*Wednesday*") -or ($DayOfWeek -like "*Thursday*") ){
start-process "C:\Program Files\Symantec\Backup Exec\bemcmd.exe" -ArgumentList "-o1 -jBEJob-BackupToTape-Diffs"
}
If (($DayOfWeek -like "*Friday*")){
Get-VBRJob | where {$_.Name -like "Weekly*"} | Start-VBRJob
}

It was a bit tricky to get the Veeam SnapIn name. All of Veeam's documentation tells you to run the Powershell console from within the Veeam GUI. That's no good for automation!!! I then remembered using Get-Module (to find modules registered with powershell that reside on a system), and its counterpart Get-PSSnapIn. Sure enough, the name came up in my list of snapins. Then, I use Get-Content to read the day of the week from the text file that run previously. If you're wondering why I just don't calculate it within this file, keep in mind that the Veeam job that finishes may get done at 11:30pm, or it may get done at 5:30am (the next day). Since there's variance between what day of the week it might be when the job finishes, I thought this was the best approach. Then we go with the IF statements: if it's M-Th, then I'm telling Backup Exec to start a job. If it's Friday, then I use the Veeam SnapIn to get my weekly backup job and start it. This was a pretty counterintuitive thing to work out: I'm speaking about this line:

Get-VBRJob | where {$_.Name -like "Weekly*"} | Start-VBRJob

The name of my Veeam backup job is "Weekly Backup", so I thought I could just do Get-VBRJob "Weekly Backup", but it didn't like that at all. So, I performed a Get-VBRJob | Get-Member to see what other properties I could use. Nothing worked, so off to Google I went, and found this method. It works!

PSA: Test your Directory Service Restore Mode Password

Directory Service Restore Mode is what you have to use in order to authoritatively restore things in active directory, like a domain controller. Or something. I've never actually used it outside of a lab until this past week, when I wanted to move where the Active Directory logs were. You can find that process here.

The shocking part came when I needed to boot into Directory Service Restore Mode (DSRM from now on, crikey). On a domain controller (and only a domain controller) you access DSRM by hitting F8 before Windows boot (just like going into Safe Mode) and then choosing DSRM from the list. I made it to safe mode, but didn't know the DSRM password. It wasn't in our password database either. Hmmmm.... and no one else knew it. It's a damn good thing that we weren't in some DR situation where we needed it!

So, please take a minute to check your DSRM password before you REALLY need it. If you need instructions on how to reset it, then look no further.