Category Archives: SQL Server
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
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!
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.
|[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
$sqlapp = New-Object microsoft.sqlserver.management.common.sqlconnectioninfo DBSERVER
$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!
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,
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
Here is the code you should have in your profile to load up the SQL stuff.
$ErrorActionPreference = "Stop"
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
throw "SQL Server Provider for Windows PowerShell is not installed."
$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
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml