Sql Server Powershell

Full Backup of User Databases with Litespeed

Quick script to show how to backup all user databases using litespeed.  This particular one was for sql server 2k (ya, I know, will upgrade this to 2008R2 on Monday).  If using it for 2005+ best change the sysdatabases reference to sys.databases. 

ALTER PROCEDURE dbo.lsp_FullBackupUserDBs(
	@BackupPath		VARCHAR(255) = 'F:\MSSQL\BACKUP\Full\User\'
)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @mkdir VARCHAR(4000)
	DECLARE @timestamp VARCHAR(50)
	DECLARE @filenamein VARCHAR(512)
	DECLARE @IDENT INT
	DECLARE @DBNAME VARCHAR(200)
	
	SELECT @IDENT = MIN([DBID]) 
	FROM sysdatabases 
	WHERE [DBID] > 0
	AND NAME NOT IN ('master', 'model', 'msdb', 'tempdb')
	GROUP BY DBID
	
	WHILE @IDENT IS NOT NULL
	BEGIN
		SELECT @DBNAME = NAME FROM sysdatabases WHERE [DBID] = @IDENT

		SET @mkdir = 'mkdir ' + @BackupPath + @DBNAME + '"'

		SELECT @timestamp = DATENAME(MONTH, GETDATE()) + '_' + DATENAME(DAY, GETDATE()) + '_'  + DATENAME(YEAR, GETDATE()) + '__' + STUFF('00', 3-LEN(HOUR), LEN(HOUR), HOUR ) + '_' + STUFF('00', 3-LEN(MINUTE), LEN(MINUTE), MINUTE )
		FROM (SELECT DATENAME(HOUR, GETDATE()) AS HOUR, DATENAME(MINUTE, GETDATE()) AS MINUTE) AS timedata

		SET @filenamein = @BackupPath + @DBNAME + '\' + @DBNAME + '_db_%DateTimePostfix.bak'
		SET @filenamein = REPLACE(@filenamein, '%DateTimePostfix', @timestamp)

		EXECUTE xp_procedure @mkdir, no_output

		EXECUTE master.dbo.xp_backup_database 
			@database=@DBNAME, 
			@filename=@filenamein, 
			@threads=3, 
			@init=1, 
			@priority=0, 
			@logging=0, 
			@affinity=0, 
			@throttle=90, 
			@buffercount=20, 
			@maxtransfersize=1048576, 
			@compressionlevel=6

	SELECT @IDENT=MIN([DBID]) 
	FROM sysdatabases 
	WHERE [DBID] > 0 
	AND [DBID]>@IDENT
	AND NAME NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution')

	END

END
GO
Advertisements

Code Formatting for Windows Live Writer and WordPress

Just to save you the trouble, here is the best code formatter for windows live writer when publishing to wordpress.  This is more of a reminder for myself so’s I don’t have to scour the web to find this again, but hopefully this will save someone else some time in trying to track down a good code formatter.  You can grab it here.

It’s the one that I’m using in the latest posts on this site.  If you scroll to the earlier posts, you’ll definitely notice the difference.

View Remote Drive Space in Powershell

Quick script to view the drive information for remote systems in powershell.  Just pass in the server name as a param.

param
(
	[string]$ComputerName = ""
)

$drives = gwmi win32_logicaldisk -filter "drivetype=3" -ComputerName ($ComputerName)
$server = @{n = "Server"; e = {$_.__SERVER}}
$free = @{n = "FreeSpace(GB)"; e = {"{0:N2}" -f ($_.FreeSpace/1GB)}}
$Size = @{n = "Size(GB)"; e = {"{0:N2}" -f ($_.Size/1GB)}}
$Label = @{n = "Volume Name"; e = {$_.VolumeName}}
$Percentage = @{n = "PercentageFree"; e = {"{0:N2}" -f `
	([Int64]$_.FreeSpace / [Int64]($_.Size) * 100)}}
$drives | select $server, $Label, DeviceID, $free, $Size, $Percentage | ft -auto

image

Manually uninstall sql server

Note, this works for Sql Server 2005 only!! If, for some reason Sql Server does not uninstall cleanly or at all, here are a few processes that you can use to
manually uninstall sql server.

If you’re on a clustered server, the first step you’ll want to take is to make sql server think it is no longer part of a
cluster. To achieve this, you’ll need to modify a key within the registry. Secondly, ensure that the data disks are
located on the node that you are attempting to remove sql server from.

Open the registry editor by typing regedit at the run command.

image

Next, navigate to HKLM\Software\Microsoft\Microsoft SQL Server\<instid>\Setup, where <instid> represents the specific instance of SQL Server 2005 being uninstalled. Under this key, set the SqlCluster value to 0.

image

If you don’t have Sql Server under the Add/Remove Programs, you’ll have to reinstall the Sql Server support tools. Navigate to the Sql Server install, and execute the SqlSupport.msi under Servers\Setup.

image

Next, open up a command prompt and navigate to c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap. Type in ARPWrapper.exe /Remove. This should start the uninstall of sql server. If you have multiple instances on this machine, only uninstall one instance at a time, otherwise the uninstall will error.

image

For more drastic measures, see this article.

Get items from sharepoint list

private ArrayList GetServerList()
{
ArrayList sNames = new ArrayList();
Guid id = new Guid(“46089E30-FCD1-45C9-9437-454754C5058D”);
SPSite SiteCol = new SPSite(id);
SPList splist = SiteCol.AllWebs[“”].Lists[“Servers”];

SPQuery oQuery = new SPQuery();
oQuery.Query = “” +
“True”;
SPListItemCollection listItems = splist.GetItems(oQuery);

foreach (SPListItem listitem in listItems)
{
sNames.Add(listitem[“ServerName”].ToString());
//string test = listitem[“ServerName”].ToString();
}
return sNames;
}

Sharepoint 2010 Edit Page Button Missing

I just fought with this for two days.  Apparently, the “edit page” button on Sharepoint 2010 only shows on regular pages, not web part pages.  Who knew.  This was a migrated Sharepoint application from 2007 to 2010, so I had no idea that the page was a web part page and not a regular page.

Sql Server Powershell Snapin

Might as well show you the code to load the Sql Server snapin for powershell as well.  I have this in my $profile, as I use the snapin constantly.

function LoadSQLSnapin()
{
 
#
# Add the SQL Server Provider.
#
 
$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
 
}
 
LoadSQLSnapin
 
 

Get ServerNames from SQL Server in PowerShell

I see a lot of posts on monitoring sql server that use a “Servers.txt” to iterate the server names.  We (and I imagine a lot of other places as well) store our server information in sql server and not a text file.  Here is how you get said list of servers directly from sql server instead of a text file:

Set-Location SQLSERVER:\SQL\SERVERNAME\DEFAULT
$Servers = Invoke-Sqlcmd -Query "SELECT
       ServerName      
   FROM dbo.[Servers];"
   
foreach($server in $Servers)
{
       Write-Host $server.ServerName
}

You’ll need to load the sql server snapin for this to work correctly.

Compare jobs with PowerShell

I’m more amazed every day at how much easier powershell makes administering sql server.  We currently have this big messy home-grown monitoring system to compare and track changes in sql server.  It’s complicated, fragmented and difficult to debug.  I just duplicated the whole job-compare section of the monitoring system with 9 lines of code.  This will tell you which jobs were added and which ones were removed.  I’ve not tested it thoroughly (nay, at all), like what happens when you change a schedule on a job or whatnot, but this is a good start:

sl SQLSERVER:\SQL\SERVERNAME\DEFAULT\JobServer\Jobs
$jobs = Get-ChildItem
$jobsPrevious = Import-Clixml -Path C:\JobsPrevious.xml
$difference = Compare-Object -ReferenceObject $jobsPrevious -DifferenceObject $jobs
$Removed = @($difference | Where {$_.SideIndicator -eq "<="} | foreach {$_.InputObject}) -join ", "
$Added = @($difference | Where {$_.SideIndicator -eq "=>"} | foreach {$_.InputObject}) -join ", "

Write-Host "Removed:  $Removed"
Write-Host ""
Write-Host "Added:  $Added"

This just does the jobs, but there’s no reason why you couldn’t apply this code to other nodes in the SMO object model.

Powershell is amazing.  If you’re not using powershell to administer SQL Server, you’re really doing yourself a HUGE disservice.  Use at your own risk.

Update:  The xml that returns from get-childitem will be huge.  Also note, that you should actually write the $jobs out to file and re-load it using the Import-Clixml in order for the compare-object to compare properly.  I’m STILL working on getting this up to snuff, but I’m currently stretched too thin to dedicate any time to this at the moment.  I’ll put up a better post once I’m done with it, but this is a good starting point.

Powershell script to check if LiteSpeed is installed

You could easily modify this to check if anything is installed really.

 
function CheckLiteSpeed($machineName)
{
       Write-Host $machineName
       $version = "";
       $reg = [WMIClass]"\\$machineName\root\default:stdRegProv"
       $HKEY_LOCAL_MACHINE = 2147483650
 
       $strKeyPath = "SOFTWARE\Imceda\SqlLiteSpeed\Engine"
       $version = $reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Version").svalue
       if($version -eq $null)
       {
 
               return "Not Installed"
       }
       return $version
}
 
Clear-Host
$destFile = "C:\LiteSpeedInstall.csv"
$servers = Get-Content "C:\Servers.txt";
 
if(Test-Path($destFile))
{
       Remove-Item -LiteralPath $destFile
}
Add-Content -LiteralPath $destFile "ServerName,LiteSpeedVersion"
 
foreach($server in $servers)
{
 
       $strVersion = "";
       $strVersion = CheckLiteSpeed($server);
       Add-Content -LiteralPath $destFile "$server,$strVersion";
 
}
 

Use at your own risk.  Not rigorously tested.

Bender out.