Step-1 : We will create Powershell Script to take backup of Databases in SQL Server.
![SCheduleJob-18](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-18.jpg)
Here is the full Script for your ready refrence.
param( $serverName, $backupDirectory )
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
$dbs = $server.Databases
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })
{
$dbName = $database.Name
$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + ".bak"
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($targetPath, "File")
$smoBackup.SqlBackup($server)
"backed up $dbName ($serverName) to $targetPath"
}
Save this Script file as ps1 extension.
You can create the script using even notepad.
Step 2: We will Create Batch file to call PowerShell scrip and to be used in Windows scheduler.
![SCheduleJob-18](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-19.jpg)
Save as .BAT file. Here is the batch script for ready refrence:
powershell -ExecutionPolicy RemoteSigned
-File "C:\User Data\SQL Backup\Tools\SQLServerBackupAllDatabase.ps1"
-serverName "INDEL-AXT5283NB"
-backupDirectory "C:\User Data\SQL Backup"
>> "C:\User Data\SQL Backup\LOG\\%date%.log"
Step 3 : Create a Windows Scheduler
Open Windows Task Scheduler.
Create New Task as shown below :
![SCheduleJob-18](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-20.jpg)
Enter Name & Description on General Tab as shown below:
![SCheduleJob-18](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-21.jpg)
On Trigger Tab create New Trigger and enter details as shown below :
![SCheduleJob-18](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-22.jpg)
On Action Tab Create Action and enter information as shown below : Here Select the batch file created in Step 2.
![SCheduleJob-18](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-23.jpg)
In Settings Tab do the setting as shown Below :
![SCheduleJob-18](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-24.jpg)
Click on OK to Save the Task and return to Task Scheduler Window.
Here you can see the newly created Task.
![SCheduleJob-18](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-25.jpg)
When Task is executed you will find the backup of databases at defined path in the script.
![SCheduleJob-26](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-26.jpg)
You can also find Log file at the path defined in batch.
![SCheduleJob-27](https://msdynamicsnavashwinitripathi.files.wordpress.com/2016/07/schedulejob-27.jpg)
Thats all for this post, will come up will more information in my up comming posts.
No comments:
Post a Comment