Category Archives: SQL Server

SQL Backup File location and date time


So, you want to see when you’ve backuped up what/where and when. Certainly something that’s worth keeping an eye on, but how do you find it? Here’s how

select server_name,
    backupset.database_name,
    backupset.backup_start_date,
    backupset.backup_finish_date,
    backupset.backup_size,
    backupmediafamily.physical_device_name
from msdb.dbo.backupset
    inner join msdb.dbo.backupmediafamily
    on backupmediafamily.media_set_id = backupset.media_set_id
order by backupset.backup_finish_date desc

 

So, if you run your backups like I do (just to a named file and not so much media sets) you could use this to do a restore!

Advertisements

Identify your Powershell Script to SQL


I recent wrote a backup script that ran asynchronously and have had some weird problems with it rarely just never stopping. I’m no SQL expert so its hard for me to diagnose on that end. I’ve popped in to SQL Analyzer to try and see what DB its hung up on and just cant figure out what processes are from my job since there are just so many. I noticed there is an Application Name listed and there were some custom names so I got looking in to this and found I can name my connections to help identify which SQL processes are from my script.

For my backup script I was using an SMO Server object which could be constructed with a SQLConnectionInfo object which happened to allow me to set the application name, here is an example of that.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null

$sqlapp = New-Object microsoft.sqlserver.management.common.sqlconnectioninfo DBSERVER
$sqlapp.ApplicationName = “Powershell Magic”

$sql = New-Object microsoft.sqlserver.management.smo.server $sqlapp

That’s all there is to it. If you still use the old style connection objects with a connection string you can specify the application name in the connection string.

Data Source=DBSERVER;Initial Catalog=TheDB;Application Name=Powershell Magic;

 

This certainly helps with debugging Powershell SQL scripts!

Neat Trick – SQL Powershell Provider


This is something rather basic but certainly worth mentioning.

Once you load the powershell SQL Provider (code below) you can access a sql servers DB’s like so,

cd SQLServer:\sql\servername\default\databases\dbname

That is to say, you change to the provider, SQLServer, go to the DB engine side of things, sql, the server, the instance which in this case is the default, then in to databases, and then the database name.

well, who wants to type that out all the time? tab complete works, but i’ve run in to problems doing it right after the server name.

So, if you are like me and often just want to poke around the databases, you can make a shortcut like so.

New-PSDrive –name ServerSQL –root SQLServer:\sql\server\default\databases –psprovider sqlserver

so then, to get to that servers SQL DB’s you can just do

 

cd servername:

Here is the code you should have in your profile to load up the SQL stuff.

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider for Windows PowerShell is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}

#
# Set mandatory variables for the SQL Server provider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml
Pop-Location