SQL Magic - Making off-server backups

So if you've ever done some budget SQL server administration, you'll see the benefit of this post. When I say "budget", I mean if you've been tasked with backing up a SQL server but don't have any fancy backup software to do the work for you. The below outlines two scripts that will do the following. It makes a full backup of a SQL database and moves it to an off-box Windows share (could be any kind of SMB share). A caveat of this script is that if you want it to run on a schedule without your intervention, then it needs to run as a user that has permissions on the destination windows share. You have to be able to connect to the destination path (e.g. \\myserver\sharename) without being prompted to login and be able to write files there. Make sure to modify the scripts to match your file names, paths, database names and server names.


Here's the scripts, first comes the Batch file (.cmd):

@echo off
REM ------------- ADJUST THE FOLLOWING TO CHANGE THE DESTINATION FOLDER NAME ---------------
set DestFolder=myDatabase Backup Folder

REM Make the folder even if it already exists
mkdir e:\MSSQL\Backup

REM Run the SQL script to do the backups.  This runs against the default SQL instance on the current machine - check the docs if you need to specify an instance.
sqlcmd -S %COMPUTERNAME% -i E:\BackupDB.sql

REM Make the destination folder on the destination server
mkdir "\\destServerXYZ\Backups\%DestFolder%\"

REM Move the backups to the destination server
move e:\MSSQL\Backup\*.bak "\\destServerXYZ.fully.qualified.domain.com\Backups\%DestFolder%\"

---

Then here's the .sql file:

DECLARE @curDate AS varchar(8), @dbPath1 as varchar(53), @filepath as varchar(128)
set @filepath = 'E:\MSSQL\Backups\';
set @curDate = convert(char(4),YEAR(getdate())) + RIGHT('00' + convert(varchar,MONTH(getdate())),2) + RIGHT('00' + convert(varchar,DAY(getdate())),2);
set @dbPath1 = @filepath + 'myDatabase_' + @curDate + '.bak';
BACKUP DATABASE [myDatabase] TO  DISK = @dbPath1 WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'myDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

Ghostly NIC

I know it's been a while since I updated this.  This post is the first of hopefully many useful tidbits to help you in your tech journey.

I ran into the following solution to get rid of those ghost network adapters that you can’t see in windows.  From time to time when working with VMs, windows will ghost a NIC and it will be still in the windows config but you can’t see it anywhere or remove it.  The following allows you to see it and the ability to remove it if needed.

1. Run the following two commands from an administrative command prompt:
   set devmgr_show_nonpresent_devices=1
   devmgmt.msc
2. This will launch the device manager.  Choose View | Show Hidden Devices from the menu bar. 
3. Find the non-present NIC(s) under Network Adapters section (usually the first one in the list of NICs).
4. Right-click it and choose Uninstall.

I can't take credit for this one myself, I found this solution on a Microsoft web site after doing a search.  I had been looking for this for a LONG time and finally found it today.

New Audio Equipment

Our church got a whole new sound and lighting kit over the last couple weeks. We were borrowing the kit from another one of our member churches and had to haul all the stuff from a school about 9 blocks away, then when service was done, pack it up and haul it all back again. Lots of work and extra cost to rent a van each week. Of course the price of a new kit would take about 5000 years to recoup the cost compared to the rental van. Anyhow, the kit includes a new (still partially wrapped) LS9-16 digital mixing console, Roland digital snake, new monitor wedges, house speakers, stands, microphones, cords, lighting stands, lights and light board. Pictured below is the sound board, easily the flagship piece of the kit. Also pictured on top is the small light board.
I've worked with a digital board before (Roland's RSS) so most of the concepts are similar but getting used to this board will take a little bit of time still. To me the function is "mute" not on/off, and "aux send" not "mix", but again similar concept. Unfortunately I have not had the pleasure of working with the board for more than 10 minutes due to scheduling and time restraints. But over the next few months I've been told that I'll be taking on the role of audio captain and the ship will be mine. Not sure if I'm really excited about it yet or not. It's a lot of responsibility to be put in that kind of role and I've done it before, for several years actually. Having a family to get out the door really early on Sunday and trek across the city can be tough sometimes but now that my son is older and can walk the whole way on his own, that will make things easier. I've been out of the role for over a year now and I know it's time to tune my ears in again and let God do His thing through me.

I did get to look through the manual some and I see this board is a good competitor to the Roland system. The biggest cool advantage I see is over remote control. The LS9 can hop right on your network and can be controlled over Ethernet to a computer running their editor software or their iPad/iPhone app. Yes, you can control a good chunk of settings (mostly mix related, not things like patching and really tweaky things) using a native IOS app. You could do this with the Roland but you had to use some remote desktop or VNC software to view the screen of the USB-connected PC, not ideal. It looks like most other features of the two systems are pretty comparable. I think Roland actually has a head start with the native support for the digital snake where we will be doing digital, back to analog, then to digital again. Technically From Mic to speaker, there will be 4 conversions going on - another thing I don't like too much but it wasn't my decision and I think the Roland board is much more money.

Streetside Gear

Interesting arrays of equipment scattered on the streets of downtown Manhattan in the last couple weeks. Seems some movie making folks want that Gotham-like look when filming buildings at night. I saw probably 5 or 6 buildings withe these lights and security guards lurking everywhere.

vCloud Director

Earlier this week I found out that VMware is discontinuing Lab Manager and pushing people to their cloud management product called vCloud Director. I won't go into specifics about what these are, but just venting that they told us when we bought it mid 2010 that they didn't think it would be replaced by this product. Now I've got yet another project on my plate to migrate this to the new tool - not a simple task I might add. If you've ever looked at the docs for it, you will know that it involves a small army of planning teams to get the storage, networking and database resources setup in most organizations, and we're only planning to put it on our Corp network, I can't imagine how much it would take to put out it in a production network where all the firewalls and subnets need to be setup.

Good thing I'm a true nerd and I'm actually looking forward to this since it's technology I've never touched before. I always jump at the chance to learn something new.

Wow, i never thought

... I'd be posting a blog from a small tablet device. A year ago, the world didn't even know what a tablet really was until Apple defined the space with the iPad. Again they are leading the pack and becoming the measuring rod everyone else uses.

PowerShell

Ok, so I know I'm not a huge proponent of Micro$oft products, but I'd have to say this one is pretty good. I just started learning powershell about a month ago for my job and I already have some pretty sweet scripts written that save me tons of time.

What is it exactly? It's the latest scripting environment from Microsoft that includes a shell (basically a command console like a DOS window on steroids) and a new scripting / command language. It's M$'s way of catching up with the UNIX world and shell scripting.

The neatest feature of the scripting language I think is the fact that everything is an object and that you can pipe objects to other object's methods to produce results. For instance, the following script shows how to write your own ping function.

$ping = new-object System.Net.NetworkInformation.Ping
$result = [System.Net.Dns]::GetHostByName("$strComputer")
$result.AddressList (PIPE) ForEach-Object {

        $Reply = $ping.send($_.IPAddressToString)
        if($Reply.status -eq "Success")
        {
  $Reply (PIPE) Select-Object Status,Address,RoundTripTime (PIPE) Format-Table
        }else{
            write-host "Host $strComputer ($_) ping FAILED with response:" $Reply.status
        }
}

Produces the following:

 yahoo.com

Status   Address        RoundtripTime
------   -------        -------------
Success  72.30.2.43                84

Status   Address        RoundtripTime
------   -------        -------------
Success  98.137.149.56             88

Status   Address        RoundtripTime
------   -------        -------------
Success  209.191.122.70            40

Status   Address        RoundtripTime
------   -------        -------------
Success  67.195.160.76             32

Status   Address        RoundtripTime
------   -------        -------------
Success  69.147.125.65             33

As you can see from this, you can also create and use objects that belong to the .Net framework. If you're any kind of developer, you can see the power in this. You can even go as far as using the Windows.Forms objects to create a GUI for your scripts and interact with the user with text boxes, lists, labels, buttons, etc. Crazy stuff!

For some reason my code above won't show the pipe character, so in it's place, I'll use "(PIPE)".