April 7, 2012 at 9:32 am
Dear All,
I am looking for a Complete solution (Script based or Powershell based) to backup SQL Server AGENT ALL objects i.e. -
* SQL Server AGENT Jobs, Jobs Steps, Jobs Schedules, Jobs Alerts, Jobs Notifications, Jobs Target,
* Same for Database Maintenance Plans & Database Mails
* SQL Server AGENT ALERTS &
* SQL Server AGENT Operators.
I searched the internet & found some solutions but, not found any one which one fulfill the Complete requirement. Like -
1. Backup-Restore MSDB db - It's not easy when Instance get changed & not able to perform this as I have all SQL servers with different instances,
,
2. Use some third party tool to synch your servers - I did not test it, because I need some in-build or custom scripts to achieve the same without depending on such tools.
Please guide me to How I achieve Backing up SQL Servers Agent ALL objects as well as Backing up Database Maintenance Plans & Database Mails.
April 8, 2012 at 6:17 am
To back that stuff up, you backup MSDB. That's it. That's where that information is stored.
Why would this be hard with different servers? You have backup routines scheduled on all your servers, right? Just backup MSDB.
Now, instead of backing up, are you trying to migrate the information from one server to another? If so, you need to script out the commands.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2012 at 3:35 am
hey hi,
PowerShell Code to Script Out All SQL Server Agent Jobs
# Date:23/02/12
# Description:PS script to generate all SQL Server Agent jobs on the given instance.
#The script accepts an input file of server names.
# Version:1.0
#
# Example Execution: .\Create_SQLAentJobSripts.ps1 .\ServerNameList.txt
param([String]$ServerListPath)
#Load the input file into an Object array
$ServerNameList = get-content -path $ServerListPath
#Load the SQL Server SMO Assemly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection
#For each server in the array do the following..
foreach($ServerName in $ServerNameList)
{
Try
{
$objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
$objSQLConnection.Open() | Out-Null
Write-Host "Success."
$objSQLConnection.Close()
}
Catch
{
Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
$errText = $Error[0].ToString()
if ($errText.Contains("network-related"))
{Write-Host "Connection Error. Check server name, port, firewall."}
Write-Host $errText
continue
}
#IF the output folder does not exist then create it
$OutputFolder = ".\$ServerName"
$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}
#Create a new SMO instance for this $ServerName
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
#Script out each SQL Server Agent Job for the server
$srv.JobServer.Jobs | foreach {$_.Script()} | out-file ".\$OutputFolder\jobs.sql"
}
SQL server DBA
April 9, 2012 at 12:55 pm
You said you already knew about backing up and restoring msdb, however did you know you could restore msdb with a name other than msdb? This would allow you to access older data without affecting that instances copy of msdb. To restore a database with a different name just specify the new name in the RESTORE DATABASE command.
Restore a Database to a New Location > To restore a database to a new location and name
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply