Powershell – Run PowerShell script within a maintenance plan

powershellsql-server-2008windows-server-2008

I have an SQL Server 2008 database, run on a 64-bit Windows Server 2008. I created a pretty simple PowerShell script that automates a bunch of tasks. I would like this script to run as part of our maintenance plan. How do I schedule it to run?

It needs to run as administrator – I know this is not ideal, but I am hoping I can have it execute as administrator. I currently open PowerShell as administrator and execute the script manually every morning.

In case it matters, the script:

Zips the backup file (7-Zip) and moves it to our backup location
Juggles some files – keeping the latest backup in a particular network location, and moving what was the most recent backup into the number two spot
Deletes the .bak files

I want the script to run without counting on me to run it.

Best Answer

You can run your PowerShell script within SQL Server 2008 Agent (SQL Server's job scheduler). Using SQL Server Management Studio, create a SQL Server job and select a PowerShell job step. In the job step information specify the full path to your PowerShell script file.

Related Solutions

SQL Server 2008 R2 backup plan not deleting files older than X days set in maintenance plan

As I understand you delete them in Maintenance plan using special Maintenance Cleanup Task .. or via "Execute T-SQL Statement Task"? Although it should not matter.

By looking at your SQL statement I think it could be the file extension: it should be without dot, just extension on it's own.

I doubt it is a permission issue as you are able to create backups in first place. But check if SQLServerMSSQLUser$... group has full access -- that is how I have on all "backup" locations (these permissions should be set up automatically if you set that folder as default backup destination during initial setup).

AFAIK xp_delete_file is undocumented and I have found no references in SQL Books to it. Apparently SQL Server checks what file it is before deleting it -- it works with own files only (to prevent wrong usage/security, I guess). Here are the format and parameters:

EXECUTE master.sys.xp_delete_file
0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
N'D:\SQLbackups\', -- folder path (trailing slash)
N'bak', -- file extension which needs to be deleted (no dot)
N'2011-06-15T15:10:04', -- date prior which to delete
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

By looking at your example SQL statement I see quite a few differences:

In first parameter you have 0 (File Backups) but attempting to delete Text file
File extension should exclude dot
You should provide expiry date (files older than that will be deleted)

Link: http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx

Powershell script not working when launched from SSH (or winexe) from a Mac or Linux Client

You seem to have two different issues preventing you from doing what you want here - ExecutionPolicy and SysWOW64 File System Redirection.

Bypass Execution Policy

To bypass the execution policy, do so when launching PowerShell, like so:

PowerShell.exe -ExecutionPolicy Bypass -File .\scriptname.ps1

Bypass File System Redirection

Since freeSSHd seems to be a 32-bit application, Windows tries to ensure no compatibility issue on your 64-bit machine, by way of a few nifty tricks (or "garbage" behavior, depending on your preconception). One way is via the File System Redirector

To disable File System Redirection, you can call the Wow64DisableWow64FsRedirection Win32 API function, and all subsequent calls from that thread will no longer be affected by redirection:

$MethodSignature = @"
[DllImport("kernel32.dll", SetLastError=true)]
public static extern bool Wow64DisableWow64FsRedirection(ref IntPtr ptr);
"@

$Kernel32 = Add-Type -MemberDefinition $MethodSignature -Namespace "Kernel32" -Passthru

$ptr = [IntPtr]::Zero
$Result = $Kernel32::Wow64DisableWow64FsRedirection([ref]$ptr)

# Now you can call 64-bit Powershell from system32
C:\Windows\System32\WindowsPowershell\v1.0\powershell.exe -ExecutionPolicy Bypass -File .\Script.ps1

Save the above as a script (wrapper.ps1), and invoke it from ssh with:

powershell -ExecutionPolicy Bypass -NoProfile -File .\wrapper.ps1

To avoid any 64-bit modules or snapins from the profile being loaded in the 32-bit session