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.