November 27, 2009 at 3:59 am
Hello.
This is my first post here, so I'd like to say hi =)
OS: Windows 2008 R2
SQL server: Sql server 2008
I've recently been put in task of administering the SQL servers in-house at the company I work for.
I'm not by any means a skilled DBA, but I think I've come up with a solution that works.
I've written some T-SQL scripts which takes backup of all our databases with Full backup once a week, Diff backup all other days, and Log backup every hour. I've scheduled these scripts in a SQL server job which has run for about a week now, and working like a charm.
Now, the plan is to move all backups to another location each week, so that I have a clean backup file for each database each week, for easy restoring databases in case of disaster.
This script looks like this atm.
#######################################
# Functions section starts here
#######################################
# powershell's Get-Date contains a bug, and can't calculate weeknumbers right
function getWeekNumber {
$(Get-Culture).Calendar.GetWeekOfYear($(Get-Date),
[System.Globalization.CalendarWeekRule]::FirstFourDayWeek,[DayOfWeek]::Monday)
}
function getMonthName([int]$monthNumber) {
$monthNamesArray = New-Object system.Globalization.DateTimeFormatInfo
$currentMonth = $monthNamesArray.MonthNames[$monthNumber - 1]
return $currentMonth
}
function writeLogEntry([string]$message) {
$date = Get-Date -DisplayHint DateTime
$logEntry = $date.ToString() + " - " + $message
$logEntry >> $logfile
}
#######################################
# Functions section ends here
#######################################
# Globals
$currentBackupDir = "H:\SQLBackup\Current"
$archiveBackupDir = "H:\SQLBackup\Archive"
$logfile = "C:\temp\moveBackupLog.txt"
$date = Get-Date
$weekNumber = getWeekNumber
# Reset variables
$thisMonth = $date.Month
$thisMonth = getMonthName($thisMonth)
$archiveBackupDir = $archiveBackupDir + "\" + $thisMonth + "\" + $weekNumber + "\"
# Check if directories exists
if (!(Test-Path -Path $logfile)) {
New-Item $logfile -ItemType file | Out-Null
writeLogEntry("Log file created.")
}
if (!(Test-Path -Path $currentBackupDir)) {
writeLogEntry("Backup directory " + $currentBackupDir + " does not exist, exiting...")
exit 1
}
if (!(Test-Path -Path $archiveBackupDir)) {
writeLogEntry("Archive directory does not exist. Creating directory " + $archiveBackupDir)
New-Item $archiveBackupDir -ItemType dir | Out-Null
}
# Get a list of files in $currentBackupDir and move them
$listOfDatabaseBackups = @(Get-ChildItem $currentBackupDir) | Where-Object {!$_.PSIsContainer} | Where-Object {$_.Extension -eq ".BAK"}
if ($listOfDatabaseBackups.length -ge 0) {
foreach($file in $listOfDatabaseBackups) {
writeLogEntry("Moving file " + $file.FullName + " to " + $archiveBackupDir + $file)
Move-Item -Path $file.FullName -Destination $archiveBackupDir | Out-Null
}
}
The plan is to move each database backup file from H:\SQLBackup\Current\dbBackup.BAK to H:\SQLBackup\Archive\<monthName>\<weekNumber>\dbBackup.BAK.
The script runs perfectly when I execute it manually, but fails for some reason when executed as a scheduled job in SQL server.
My SQL server agent service is running as a local user sqladmin, and I've tried executing the job as both local 'Administrator', 'sqladmin' user, and database admin user 'sa'. I'm suspecting there's a lack of understanding how the sql agent works from my side, and I've spent a couple of days trying to find a solution without success.
When I try to execute the job from SQL Server management console, with 'Start Job at step', I only get an error, and the Windows event log says:
SQL Server Scheduled Job 'Test' (0x501BA679C4DFF84C804486899EEF025A) - Status: Failed - Invoked on: 2009-11-27 11:54:41 - Message: The job failed. The Job was invoked by User sa. The last step to run was step 1 (TEst).
Please give me a hint on how to solve this.
Thanks.
Rolf Blindheim
November 27, 2009 at 4:59 am
remember that for any sql job, if you do anything regarding shares, files or folders. the sql agent account will need to have permissions for access and modification, your sql agent account should really be a windows domain account, with the minimum priviledges to do what is required.
In some organisations, the same account is used for sqlserveragent and the mssqlserver service.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 27, 2009 at 5:54 am
Hello, thanks for quick reply.
I've found a temporary workaround by adding the job as job type 'Operating system (CmdExec)'
I just start the script with: powershell -Command "& <path to script\moveBackup.ps1".
This allows the job to complete, but I'm still wondering how to do this "the right way".
So if anybody know, please let me know.
-Rolf
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply