SQL Database back up scheduler in .ps1 code

-
$serverName = "WEBSVR1\WEBSVRSQL"
$backupDirectory = "E:\Backup\MSSQL_Daily"
$daysToStoreBackups = 2

 [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

 Get-ChildItem "$backupDirectory\*.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreBackups)} |% {Remove-Item $_ -force }
"removed all previous backups older than $daysToStoreBackups days"

 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"
               
}

Comments

Post a Comment

Popular posts from this blog

How can we find .net version in server or computer

-
Save following code as   xxxx.bat and double click to execute. @echo off SETLOCAL ENABLEDELAYEDEXPANSION if   "%1"=="/?" GOTO :DO_HELP if /I "%1"=="-h" GOTO :DO_HELP if /I "%1"=="-v" GOTO :DO_REG_INFO GOTO :DO_RELEASE_VER :DO_HELP echo Usage: %0 [ -v ^| -h ^| ^/? ] echo            Default: Returns the installed DotNet 4 version. echo -v         Verbose: Returns the all the DotNet 4 registry settings. echo -h or /?   This help screen. echo see: https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/how-to-determine-which-versions-are-installed echo. echo Release Number Ver.   Installations echo -------------- -----  ------------------------------------------------- echo 461310 0x709fe 4.7.1  All other Windows OS versions echo 461308 0x709fc 4.7.1  On Windows 10 Fall Creators Update echo 460805 0x70805 4.7    All other Windows OS versions ...
Read more

Develop recaptcha validation in c# backend and website

-
In Html  <div class="g-recaptcha" data-sitekey="@siteKey"></div>                                     <script src="https://www.google.com/recaptcha/api.js?hl=en" async defer></script> C# backend string EncodedResponse = Request.Form["g-Recaptcha-Response"]; bool IsCaptchaValid = (ReCaptchaClass.Validate(EncodedResponse, secretKey) == "True" ? true : false);  public class ReCaptchaClass     {         public static string Validate(string EncodedResponse, string secretKey)         {             var client = new WebClient();                   var GoogleReply = client.DownloadString(string.Format("https://www.google.com/recaptcha/api/siteverify?secret={0}&response={1}", secretKey, EncodedResponse));       ...
Read more

Download csv file in client side with mvc controller

-
1) This is controller action method public ActionResult FeedbackExport(List<string> guidList)         {             string fileName = "CDLmallFeedback.csv";             List<ContactUsViewModel> feedbackFullList = new ContactUsManager().GetAllFeedbackHistory().ToList();             if (guidList != null && guidList.Count() > 0)             {                 feedbackFullList = feedbackFullList.Where(x => guidList.Contains(x.GUID)).ToList();             }             string delimiter = ",";             int feedbackLength = feedbackFullList.Count();             StringBuilder sb = new StringBuilder();             sb.AppendLine(st...
Read more