Powershell: Backup Files to Tape

I had a maintenance plan on a server with a client that was having issues running successfully each night because it ended up filling the drive. This as it turns out ended up being that the clean up task was set to delete backups older than 1 day. Ok you would think that was sufficient, however it appears in this instance that was not working. The time stamps showing for the previous day backup was within a minute of the next run of the maintenance plan. So I took it to mean that it was not actually seeing them as over a day old and therefore skipped them. I decided to change it to 21 hours instead of one day and that seemed to work for this client.

Either way I needed to delete the old backups that I know were already backed up to tape. To do this I wrote up this function that will simply return a bit of information on the file like name, last access, last write, and attribute values. The attribute will show “Normal” if the file has been backed up (or the archive bit cleared) and “Archive” if it still has to be backed up.

Now I would caution you to do your own verification as to whether the file actually exist on your backup media (tape, disk, etc.). There are multiple things that can modify the archive bit, so use at your own discretion.

function Verify-FileBackedUp ($rootdir,[switch]$recurse)
{
  # if value "archive" is shown means file has not been backed up
  # if value "normal" is shown means file has been backed up, or the archive bit cleared 
 if ($recurse)
 {
  dir $rootdir -recurse | where {-not $_.PSIsContainer} | 
  select FullName, LastAccessTime, LastWriteTime, 
   @{Label="FileBackedUp";Expression={(Get-ItemProperty $_.FullName).Attributes}}
 }
 else
 {
  dir $rootdir | where {-not $_.PSIsContainer} | 
  select FullName, LastAccessTime, LastWriteTime, 
   @{Label="FileBackedUp";Expression={(Get-ItemProperty$_.FullName).Attributes}}	
 }
}

A sample output would look something like below:

capture

SQL Server Data Tools: A spork of sorts (part two)

In the previous post (a bit of a rant too) I went over SSDT and how it has evolved for whatever reason by Microsoft into SSDT (Database development) and SSDT-BI (Business Intelligence development). In this post I wanted to go over how you actually get this installed so you can start the fun work of working with SQL Server 2014.

If during the CTP phases of SQL Server 2014 releases you may have caught the blog post on SQL Server Data Tools – Business Intelligence (SSDT BI) for SQL Server 2014 CTP1 (another interesting post to read response to this in the comments). This is where it was told that you will not be able to install SSDT BI on a new machine with SQL Server 2014 installation media. It became a separate download. You would get this download from Microsoft SQL Server Data Tools page on MSDN, scroll down to the bottom of the page to find SSDT-BI download links for VS 2012 and VS 2013.

I opted to just go ahead and move to Visual Studio 2013 version and downloaded that from here. If you have installed the version for VS 2012 before it has not changed from that installation process. I will go over the installation for VS 2013 version to just provide a walkthrough for those that may be just starting out.

You start out with a file named “SSDTBI_x86_ENU.exe, yeah no 64-bit version still. After the download is complete just double click. Now this is a basic clicking next through the wizard and I will just point out a few notes from my experience installing this on a new Windows 7 machine.

  • The file downloaded is going to extract the files to a directory of your choice. If you end up having to cancel and start over, you go into that extracted directory and run the setup.exe file. You are actually going to get the SQL Server Installation Center similar to this:
    Capture
    Just click on “New SQL Server stand-alone installation…” and it will bring you to the starting point of accepting the license terms.
  • It states in the feature selection that .NET Framework 4.5 is installed through this media. In my instance it actually did not install this and failed with a feature rule check. Might be worth verifying that is installed if you are on a new machine.
    Capture2
  • Also note above what it is also going to install, Visual Studio 2012. If you don’t believe it, check out my own laptop:
    Capture4

Once you get done with the installation you are ready to get started with Business Intelligence development.

Capture3

Happy learning!

SQL Server Data Tools: A spork of sorts (part one)

Prior to SQL Server 2012 the Business Intelligence (BI) toolset was made up of just Business Intelligence Development Studio (BIDS). Easy to install as it was part of the SQL Server installation media. Now with database development tools you had to go the route of getting a license for Visual Studio where it allowed you to create database projects. Oh, how that is all changing now, and in some ways go back in time.

SQL Server Data Tools (SSDT) brought about changes in BI and database development. First Database Development changed to SQL Server Projects and no longer required a fully licensed version of Visual Studio, it used the integrated shell. It stayed this way up until Visual Studio 2013. With VS 2013 it now appears it might have gone back to requiring a licensed version of VS. A bit more on this part below.

Business Intelligence development also changed tools from BIDS to SQL Server Data Tools. Wait, wasn’t SSDT for database development? It is actually for both but actually require separate installs. It is now being referred to as SSDT-BI. Nice work there Microsoft.

If you have installed SSDT for SQL Server 2012 you will note that it actually is only installing SSDT-BI by viewing the new project window:

Capture2

What?!!?? You want me to install the same thing again? Well it appears that even though they share the same title of application they are now separate. If you read the blog post on MSDN, SSDT and Visual Studio Versions, and note the comments. I particular liked this one:
Capture3

What that means is SSDT is now something that encompasses two things: Database Development and BI Development (reference a fork + spoon = spork) but two different teams are developing each one. Glad that confusion is over.

Now if you go to the page for Microsoft SQL Server Data Tools it will provide al ink to “Download Visual Studio 2013 with SQL Server Tooling” that basically points you to the Visual Studio 2013 product page. Which I have installed VS 2013 for Desktop and no where can I find how to make it update to include SQL Server Tooling. If you visit the Visual Studio 2013 production comparison to Visual Studio Online and view under “Development Platform Support” you will notice that SSDT only shows up for Pro, Premium, and Ultimate Editions. So they went back to 2010? So right now it appears if you want to do database development stick with VS 2012 and down. I think VS 2012 includes support for SQL Azure development but I have not dove into that as of right now.

In the next post I will go over some things with SSDT-BI and how installation for this changed.

SQL Server Prerequisites .NET Framework

As of SQL Server 2012 the .NET Framework prerequisite of .NET 3.5 SP1 is no longer installed for you by the SQL Server installer if it is found to be missing from the server. Now with Window Server 2008 R2 SP1 or higher this is simply enabling the feature within Windows Server, no actual installation. This can be easily accomplished with PowerShell as a quick script to run prior to doing the installation of SQL Server.

The PowerShell Windows cmdlet that is used in this instance will be Get-WindowsFeature and Install-WindowsFeature (Windows Server 2008 R2 you will use Add-WindowsFeature).

The .NET 3.5 SP1 feature in Windows Server is referenced as “Net-Framework-Core” in PowerShell. You can find this by calling the command:

Get-WindowsFeature Net*

NETFrameworkCore_ps

The below script is going first verify it is not installed and then will enable the feature (or technically install it I guess). In order to do this though you will need the OS media as you have to pass in the source path to “<drive letter>:\sources\sxs”. Note: You have to execute this in an elevated PowerShell console, so “Run as Administrator”.

#verify installed first
Get-WindowsFeature Net-Framework-Core).Installed

#Add feature
Install-WindowsFeature Net-Framework-Core -source 'G:\sources\sxs'

When you run this command you will see a status “bar” of sorts appear while it is performing the installation. Once completed you should see output that lets you know it was successful.

Now if you happen to see any errors you might reference the KB 2734782 – .NET Framework 3.5 installation error as there are various reasons why this command might fail.

 A small update to this post…

I recently came across a server that I was getting the “0x800f0906″ error and went through the article above verifying Internet access from the server and having mounted the ISO for the OS properly. I came across a blog post that brings up the possibility that you need to update the local source media on the server.

The treasures of the SQL Server Error Log

I am fairly active on Database Administrators QA site on StackExchange.com.  On average, most questions you find folks asking are about troubleshooting some error they are getting running code or some application is returning. I have noticed one of the most common comments we end up adding to a question is “have you looked in the error log” or “what error messages show up in the error log”, at least with SQL Server related questions.

SQL Server error log is something that every DBA should know how to search and review regularly for the instances in their responsibility. SSMS (GUI) is a common method to look through the error log for an instance, but is only efficient for one instance at a time. Another common method is an undocumented procedure that came around in SQL Server 2005: sp_readerrorlog. I will not go over that one as it is covered pretty well in this MSSQLTip.com article.

Now I am partial to using PowerShell because it offers a way to perform the same task against one or multiple instances with the a few keystrokes. Now you likely know you don’t necessarily start out with a few keystrokes. I have picked up that if you plan on doing everything more than once, you write it where it only takes you a few keystrokes the next time.

SQL Server Management Objects offer a fairly easy method for pulling the error log for an instance. Creating a new object to an instance and passing it to Get-Member you can see the definition shown:

System.Data.DataTable ReadErrorLog(), System.Data.DataTable ReadErrorLog(int logNumber)

By default this will return the latest error log for an instance. You can pass in an integer value for older error logs based on how many the instance is configured to keep. Which by default will be 6 (six), so entering a value from 1-6 will give you that particular error log.

$srv = New-Object Microsoft.Sqlserver.Management.Smo.Server ORKO\SQL12
$srv.ReadErrorLog()

Capture1

If you do a Get-Member on the method you can see the property values that you can base a filter on: LogDate, ProcessInfo, Text. You can obviously do filtering even easier passing this to Out-GridView, but I will let you play with that on your own.

As an example, say I am reviewing the error log file for possible login issues and I want to return only those failed attempts:

$srv = New-Object Microsoft.Sqlserver.Management.Smo.Server ORKO\SQL12
$srv.ReadErrorLog() | where {$_.ProcessInfo -eq &quot;Logon&quot;}

Capture2

Now if you have an instance configured to log successful and failed login attempts you would want to filter the Text on “failed” to limit the results. As well your error log would obviously be a considerable size on active instances so it can take some time using this method, but it offers a nice break.

Now as I stated previously, writing something the first time so after that it is only a few keystrokes, means you would setup a function to call the code above. I do this with a few extra things added for my purposes, and have it in PowerShell profile so it is available more easily. Below is the current function I use in my profile on a regular basis:

function Get-SQLErrorLog ($server,$LogNum,$HowMuch,[switch]$filter)
{
$srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
switch ($LogNum)
{
0 { if ($filter)
    {
	# Filter log to exclude:
	#- successful db/log messages 
	#- login failed
	#- login succeeded
	#- Information message about runtime of instance (e.g. "This instance of SQL Server has been using process ID...")
	Write-Host "Filtered SQL ERRORLOG on $server" -ForegroundColor Red
	$srv.ReadErrorLog(0) | Where { $_.Text -notmatch 'Login failed' -and $_.Text -notmatch 'Login succeeded ' -and $_.Text -notmatch 'Error: 18456' -and $_.Text -notmatch 'backed up' -and $_.Text -notmatch 'This instance of SQL Server'}
     }
     else
     {
	$srv.ReadErrorLog(0) | Select -Last $HowMuch
     }
}
1 {
     if ($filter)
     {
	# Filter log to exclude:
	#- successful db/log messages 
	#- login failed
	#- login succeeded
	#- Information message about runtime of instance (e.g. "This instance of SQL Server has been using process ID...")
	Write-Host "Filtered SQL ERRORLOG on $server" -ForegroundColor Red
	$srv.ReadErrorLog(1) | Where { $_.Text -notmatch 'Login failed' -and $_.Text -notmatch 'Login succeeded ' -and $_.Text -notmatch 'Error: 18456' -and $_.Text -notmatch 'backed up' -and $_.Text -notmatch 'This instance of SQL Server'}
     }
     else
     { $srv.ReadErrorLog(1) | Select -Last $HowMuch }
}
2 {
     if ($filter)
     {
	# Filter log to exclude:
	#- successful db/log messages 
	#- login failed
	#- login succeeded
	#- Information message about runtime of instance (e.g. "This instance of SQL Server has been using process ID...")
	Write-Host "Filtered SQL ERRORLOG on $server" -ForegroundColor Red
	$srv.ReadErrorLog(2) | Where { $_.Text -notmatch 'Login failed' -and $_.Text -notmatch 'Login succeeded ' -and $_.Text -notmatch 'Error: 18456' -and $_.Text -notmatch 'backed up' -and $_.Text -notmatch 'This instance of SQL Server'}
      }
      else
      { $srv.ReadErrorLog(2) | Select -Last $HowMuch }
}
}
} # End Get-LatestSQLErrorLog

CTRL plus ALT plus what?

Depending on how far back you go in the technology field you all remember having to use CTRL+ALT+DELETE to change your password for your domain account.

change-password

Well, I always remote into servers now a days with consulting for various clients. I never like to keep the password assigned and generally will attempt to change it using PowerShell:

$cn = gpresult /R | Where {$_ -match “CN”} | Select -Skip 1 | clip
$acct = [adsi]“LDAP://$cn”
$acct.psbase.invoke(“SetPassword”,”MyNewPassword”)
$acct.psbase.CommitChanges()

Most of the time though I get access denied so how can you change it over VPN, when your machine is not part of their domain? Well starting in Windows Server 2008 (I think) Microsoft added a new key sequence that acts like CTR+ALT+DELETE for that machine.

You can now execute CTRL+ALT+END to get to the prompt to change your password. On Window Server 2012 it looks similar to this:

Capture

Click on “Change a password” and you get the similar prompt to older versions of Windows:

Capture

SQL Server Activity Monitor Script

You ever wonder what goes in the background of Activity Monitor? Me neither, I just used it every so often when I wanted a quick peek at what was going on for a server. Microsoft has actually made changes to Activity Monitor in SQL Server 2012 Service Pack 1 that can cause errors, and requires you to modify OS level permissions [Reference]. I work over VPN supporting clients remotely and it never been that useful to me.

I liked all the information it showed but just did not like having to use the UI it provided to filter and such. So I did a trace against an instance while I opened Activity Monitor to pull out what query it was running to populate the active sessions.

So this post is pretty much just a bookmark for the script:

SELECT [Session ID] = s.session_id
	,[User Process] = CONVERT(CHAR(1), s.is_user_process)
	,[Login] = s.login_name
	,[Database] = ISNULL(db_name(p.dbid), N'')
	,[Task State] = ISNULL(t.task_state, N'')
	,[Command] = ISNULL(r.command, N'')
	,[Application] = ISNULL(s.program_name, N'')
	,[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0)
	,[Wait Type] = ISNULL(w.wait_type, N'')
	,[Wait Resource] = ISNULL(w.resource_description, N'')
	,[Blocked By] = ISNULL(CONVERT(VARCHAR, w.blocking_session_id), '')
	,[Head Blocker] = CASE 
		WHEN r2.session_id IS NOT NULL
			AND (
				r.blocking_session_id = 0
				OR r.session_id IS NULL
				)
			THEN '1'
		ELSE ''
		END
	,[Total CPU (ms)] = s.cpu_time
	,[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024
	,[Memory Use (KB)] = s.memory_usage * 8192 / 1024
	,[Open Transactions] = ISNULL(r.open_transaction_count, 0)
	,[Login Time] = s.login_time
	,[Last Request Start Time] = s.last_request_start_time
	,[Host Name] = ISNULL(s.host_name, N'')
	,[Net Address] = ISNULL(c.client_net_address, N'')
	,[Execution Context ID] = ISNULL(t.exec_context_id, 0)
	,[Request ID] = ISNULL(r.request_id, 0)
	,[Workload Group] = N''
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT JOIN sys.dm_os_tasks t ON (
		r.session_id = t.session_id
		AND r.request_id = t.request_id
		)
LEFT JOIN (
	SELECT *
		,ROW_NUMBER() OVER (
			PARTITION BY waiting_task_address 
ORDER BY wait_duration_ms DESC ) AS row_num FROM sys.dm_os_waiting_tasks ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1 LEFT JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id) LEFT JOIN sys.sysprocesses p ON (s.session_id = p.spid) WHERE s.is_user_process = 1 ORDER BY s.session_id;

Follow me on Twitter

Follow

Get every new post delivered to your Inbox.

Join 36 other followers