T-SQL Tuesday #065 – Teach Something New

 I’ve been writing T-SQL Tuesday posts for years and it is pretty cool to be hosting this month. I don’t make it every month, but that is one of the great things about T-SQL Tuesday – if you miss it there is another one coming up quickly. I debated what the topic should be for some time and I was really leaning towards something on the cloud or PowerShell, maybe PowerShell in the Cloud, or perhaps some Cloudy PowerShell, but after Ed Leighton-Dick (B|T) announced his new blogger challenge I didn’t want to limit the options for all of these new contributors. The topic this month is straight forward, but very open ended. Continue reading

Winds in the East (T-SQL Tuesday #61)

This month’s host for T-SQL Tuesday is Wayne Sheffield (blog|twitter). Since it is the season of giving he has asked the #sqlfamily to write about how we plan to give back to the SQL community in the coming year.

Because my term on the PASSMN board is ending I may actually end up giving a little less of my time to the SQL Community next year, but there is at least one new development I am excited to share.

Continue reading

Lets Hear it for the Board (T-SQL Tuesday #59)

This month’s host for T-SQL Tuesday is the Real SQL Guy, Tracy McKibben (blog|twitter). In honor of Ada Lovelace Day Tracy has asked us to write about our heroes. I think Ada Lovelace is a great example of a hero and an inspiration as the first computer programmer, but I’ve decided to make my focus a little more local.

A little over two years ago I was starting to get more involved in PASSMN. The people on the PASSMN Board inspired me with their commitment and willingness to donate their time and knowledge to the Minnesota SQL Server community. At the time I didn’t know the board members as people. I viewed them with a certain amount of awe and nervously volunteered to speak and to help with events like SQL Saturday. It was easy to see them as heroes and they inspired me to push myself.

Maybe it is all of the activity these days around planning the current SQL Saturday, but I realized that I am now working side by side with some of these amazing people. My heroes are my fellow board PASSMN board members. They all do work that no one ever knows about and put in hours of volunteer time to help create the great SQL Server community we have here in Minnesota. I can’t begin to list all of the work these people do, but I want everyone to know how great they are. In no particular order here are the PASSMN Board heroes:

Continue reading

All of This Has Happened Before (T-SQL Tuesday #48)

This month’s host for T-SQL Tuesday is the SQLChicken, Jorge Segarra (B|T). He has asked us to write about our take on this whole Cloud thing.

I’ve been lucky enough to have some time to play with Windows Azure over the last couple months and I have some opinions on the platform. Overall I think my opinion on the idea of The Cloud is about the same as when I started. To begin with I wish they hadn’t called it The Cloud – or maybe that it hadn’t stuck as a part of the technical conversation. I think the name conjures up this romantic image for business users of a technology solution that exists out there – somewhere over the rainbow.

Continue reading

I Hope You Are Happy – T-SQL Tuesday #41

This month’s host for T-SQL Tuesday is Bob Pusateri (B|T) and he has a very timely question for those of in the SQL community here in the Midwest. As I mentioned in my last post I will be presenting three times this month and there is also the PASS Business Analysis Conference in Chicago this week. With all of that great SQL knowledge being shared, Bob has asked us to talk about why we do what we do. Why do we enjoy presenting so much?

There is a factoid that people like to throw out all the time about how people fear public speaking more than death. So why would I and my fellow SQL Speakers choose to get up and present to a live studio audience? For most of us we are volunteering our time so it can’t be for the wealth. It might be a little bit for the fame, but if adulation from the masses is what you seek there is probably a better path. That leaves happiness as the last big motivator and in a round about way I think that might be the answer. I present because it makes me happy.

Continue reading

Powershell Basic SQL Backup – T-SQL Tuesday #39

Wayne Sheffield (B|T) is hosting this month’s T-SQL Tuesday and he has asked us to talk about Powershell. He is currently doing a blog series – A Month of Powershell so his topic is not too much of a surprise.

I have dabbled with powershell since it came about and I do like scripting languages in general. However, in the IT world there are always eight ways to do everything and I don’t always turn to powershell for SQL tasks. There is one area that I created some quick scripts which I can modify easily for a lot of situations – Backups and Restores. The same things could be accomplished with T-SQL scripts, but I can easily use powershell to read the file system and do complex conditional statements across SQL instances. I have a very basic powershell script that uses the new Backup-SQLDatabase cmdlet that I can expand upon as needed. The Backup-SQLDatabase cmdlet was added in SQL Server 2012 and is essentially a wrapper that is calling the .Net SQL Management Object (SMO) for a SQL Backup.

I know when I am trying to learn something in powershell I like to see a simple version first and then as I learn I can expand on the script. That isn’t to say that I don’t borrow from other people’s scripts when they have already figured out how something works, but I know I learn more I create something on my own. With that in mind I have a simple script that you don’t need to be a powershell wizard to be able to run. The script below is very basic, but it could be done in even a single command. All I have added is some formatting so I can get a descriptive filename and media set name.

param([string]$db ,[string]$Server, [string]$dest);

$timestamp = Get-Date -format yyyy_MM_dd_HHmmss;
$Dest = $Dest + "\" + $db + "_full_" + $timestamp + ".bak", "File";
$BackupSetName = "Full backup of " + $db + " " + $timestamp;

Backup-SqlDatabase -ServerInstance $Server -Database $db -BackupFile $Dest -CopyOnly -Initialize -Checksum -CompressionOption On -BackupSetName $BackupSetName

In the first line I am using Parameters so that I can call this script from the command line or more often a .bat file and just enter the database, SQL Server Instance, and the file location for the backup. In the second line I am grabbing the Date and formatting so I can use it as part of the filename.

Next I format the destination file name and the BackupSetName. I could do this all on one line or even when I pass the parameter to the cmdlet, but this makes it easier to read.

Lastly I call the Backup-SQLDatabse cmdlet to perform the backup. I pass in several parameters to get the backup that I want. If you are accustomed  to using the T-SQL backup commands these parameters should all look familiar.

  • -ServerInstance  = The SQL Server instance where I am performing the backup. Since the server is a parameter I can easily run this script from any machine that has SQL Server 2012 installed (as long as network permissions allow)
  • -Database = The database to be backed up. The command can also be used to do a log backup or a file backup, but I typically am doing the entire database when doing these adhoc backups.
  • -BackupFile = The filepath and name for the file to backup to.
  • -CopyOnly = Typically when I am using a script like this it is adhoc and I don’t want to break the backup chain I have with my normal maintenance scripts.
  • -Initialize = A flag that says to overwrite the backup file if it exists.
  • -Checksum = A flag that sets a checksum value for validation.
  • -CompressionOption – Creates a compressed file which is useful since the next step after these types of backups is usually to restore to another database. This way the file I am copying around is not as large.
  • -BackupSetName = A descriptive name for the backup media set that is recorded in the msdb.dbo.backupmediaset system table.

To run the script I save it in the file c:\PSScripts\bu_AdHoc.ps1 and I use the following command:

sqlps c:\PSScripts\bu_AdHoc.ps1 -db Testing -Server DemoServer -Dest C:\Backups

As I said this is the basic version of this script and I can expand on it do things like backup up only at certain times or only if there are no backup files within a certain timeframe. I can easily loop through all of the databases on a server or on a list of servers passed in from a file. Or other options that I haven’t thought of yet.

An easier to read version of the script can be found in the Code section under Basic Powershell SQL Backup 2012. If you are still running SQL Server 2008 (as I am on most of the machines I work with) I have a version of the script that calles the SMO objects directly – Basic Powershell SQL Backup 2008.

Who Watches the Data Watchers? T-SQL Tuesday #30

Chris Shaw (B|T)  is hosting this month’s T-SQL Tuesday and he has asked us to discuss DBA Ethics.

For a good portion of my career I was responsible for the payroll data of hundreds of customers. In the beginning it seemed a very solemn responsibility, but I quickly got involved in the day to day and payroll data was just data. We kept it safe and made sure only the right people had access, but this is true of all of our data, right? Our main application was from a third-party vendor and it was a locally installed application that used SQL Server as the back end. About 1 in 4 new clients would ask the same question when we would set them up with the payroll software.

How do I keep the IT department/DBA from seeing the payroll data?

Continue reading