I have been to three SQL Saturdays in April and each one was great, but because I have been so busy I haven’t had much time to reflect on things. I’m not sure May is going to any less busy, but I wanted to get some thoughts down about SQL Saturday #175, because it is sure to be legendary. It was the first SQL Saturday in North Dakota and hopefully the spark for the a user group in Fargo. To accommodate the need for a quick post and the desire to document this experience I am going to resort to the time tested internet format – the top 10 list.
Top 10 Things That Were Awesome About SQL Saturday Fargo
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.
On Saturday April 6th: I will be presenting at SQL Saturday #206 in Madison, WI.
On Saturday April 11th: I will be presenting at SQL Saturday #211 in Chicago, IL.
On Saturday April 27th: I will be presenting at SQL Saturday #175 in Fargo, ND.
End of last year: I switched to the consulting world and I am now an Information Management Consultant at Digineer.
Earlier this year : As of January 1, 2013 I was assigned the role of Director of Program Development for the PASSMN Board. We’re looking for a few good SQLGeeks to present.
On Saturday April 6th: I will be presenting at SQL Saturday #206 in Madison, WI.
On Saturday April 11th: I will be presenting at SQL Saturday #211 in Chicago, IL.
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 $BackupSetNameIn 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:\BackupsAs 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.
SQL Server MVP Deep Dives 2, Chapter 60
BI Solution Development Design Considerations- by Siddharth Mehta (B|T)
This is the first chapter in Deep Dives V2 that I read which felt like I was reading a textbook. I really wanted this chapter to be something I could hand to a manager, DBA, or developer who hadn’t worked on a Business Intelligence project and they would come away with a good understanding of what it was and all of the pieces needed to build it. Essentially I wanted something the me from 3 years ago could have read and not have to learn everything on the job.
Figure 1 Typical BI design for an OLTP-to-dashboard solution from Chapter 60 of SQL Server MVP Deep Dives V2
Last month: I published an article on SQLServerPro called Combining CDC and SSIS for Incremental Data Loads.
This Saturday 9/29: I will be presenting at SQL Saturday #149 in my own Twin Cities.
Friday 10/19: SQL Friends with Dan English (B|T) at the Edina Grill.
Next Month : I am Running for the PASSMN Board. Vote for Mike! You know if we have to vote or anything.
SQL Server MVP Deep Dives 2, Chapter 10
Discover your servers with Powershell and SMO - by Joe Webb (B|T)
I’m finally back to my chapter reviews and I restarted with a fun one. I know enough to be dangerous with powershell, but I don’t use it enough to keep it fresh in my brain. Right off I liked the idea of this chapter because I got to play with scripting and do something a little different (at least for me). I know there are other ways of gathering this type of data us ing T-SQL (Brent Ozar’s sp_blitz being the first one that comes to mind), but I haven’t done it with powershell before and I certainly never used powershell with Excel before.
It is easy to complain about our jobs. Spend enough time doing something and it just can’t be wonderful all the time or probably even most of the time. Hopefully it is good some of the time or at least enough that you don’t hate getting up every day. I may not have found my dream job yet, but there are definitely things I enjoy about where I work.
Saturday 6/2: I will be presenting at SQL Saturday #117 in Columbus.
Friday 6/22: SQl Friends with Jason Strate (B|T) at Hells Kitchen (B|T). (I know this was in the last SQL Life post, but it is worth mentioning again.)
