Hi everyone!
As a database administrator, you always need a copy of your data and data processes for:
##migrating jobs from one server to another with the replace command
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=msdb;Integrated Security=True"
$s = $sqlServer
$SQLServer = "YourServerName"
##b/c this is powershell directly reference assembly
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$s = New-Object Microsoft.SqlServer.Management.Smo.Server "localhost"
$file = "\\YourNetworkPath\SQLAgentJobs\SqlAgentJobs_PROD01.sql"
Clear-Content $file
##clear-content is to empty the file before exporting SQL agent jobs
foreach ($j in $s.JobServer.Jobs)
{
$jName = $j.Name
$jFile = $file + $jName + ".sql"
$c = $j.Script()
$c = "USE msdb GO" + $c
$c | Out-File $file -Append
}
##email confirmation
Invoke-Sqlcmd -query "EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourDatabaseMailProfile',
@recipients = 'YourTeamEmail@yourCompany.com',
@body = 'Hello,
This email confirms that all SQL Agent jobs on YourServerName have been exported to:
\\YourNetworkPath\SQLAgentJobs\SqlAgentJobs_PROD01.sql
This job runs on a bi-weekly schedule at 10am on the 15th and the last day of the month.
Thank you,
@subject = 'SQL Agent Jobs have been exported from PROD01' ;"
##-ServerInstance "YourServerName"
You're all set!
If you have any questions related to this post, please put them below.
Thank you and Happy reading,
-marshé hutchinson
#learnSQLwithme
As a database administrator, you always need a copy of your data and data processes for:
- migrating servers
- disaster recovery
- backup procedures/best practices
This post covers a simple Powershell script. The script is to take a backup of all the SQL agent jobs on your SQL server instance.
If you ever needed to recreate all the jobs on another server, all you would have to do is execute the SQL script.
I. In SSMS (SQL Server Management Studio), create an empty .SQL file and save it with this naming convention:
I.e. SqlAgentJobs_PROD01.sql } This file will represent all the SQL agent jobs on the primary production SQL server.
II. Copy the "SqlAgentJobs_PROD01.sql" file from above to a network share.
III. Run the Powershell script below in Powershell ISE as administrator:
##migrating jobs from one server to another with the replace command
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=msdb;Integrated Security=True"
$s = $sqlServer
$SQLServer = "YourServerName"
##b/c this is powershell directly reference assembly
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$s = New-Object Microsoft.SqlServer.Management.Smo.Server "localhost"
$file = "\\YourNetworkPath\SQLAgentJobs\SqlAgentJobs_PROD01.sql"
Clear-Content $file
##clear-content is to empty the file before exporting SQL agent jobs
foreach ($j in $s.JobServer.Jobs)
{
$jName = $j.Name
$jFile = $file + $jName + ".sql"
$c = $j.Script()
$c = "USE msdb GO" + $c
$c | Out-File $file -Append
}
##email confirmation
Invoke-Sqlcmd -query "EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourDatabaseMailProfile',
@recipients = 'YourTeamEmail@yourCompany.com',
@body = 'Hello,
This email confirms that all SQL Agent jobs on YourServerName have been exported to:
\\YourNetworkPath\SQLAgentJobs\SqlAgentJobs_PROD01.sql
This job runs on a bi-weekly schedule at 10am on the 15th and the last day of the month.
Thank you,
@subject = 'SQL Agent Jobs have been exported from PROD01' ;"
##-ServerInstance "YourServerName"
You're all set!
If you have any questions related to this post, please put them below.
Thank you and Happy reading,
-marshé hutchinson
#learnSQLwithme
Comments
Post a Comment