Schedule daily SQL Database Backup

-
Hi guys,

In practical cases we require backup and restore facility to our database which provides an essential safeguard for protecting critical data stored in SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. Backup and restore strategy always protects databases against data loss caused by a variety of failures.

Purpose and Scope:

In this blog, I am going through step by step that will allow

  • Users to schedule the backup to be taken on a particular interval

  • Delete the backup copies after a certain period of time


Prerequisites:

  • SQL server 2000/2003/2008/2012

  • SQL server Agent (Up and running state)


Steps: Schedule the database backup Steps:

  1. Make sure that SQL agent is in running state: Go To SQL Server Configuration Manager > SQL Server Services > Run SQL Server Agent (Set it to Run Automatically)

  2. If you are using new versions of SQL server then, Go To SQL Server Management Studio, Find TAB Management > Maintenance Plans. Right Click > Maintenance Plans Wizard.

  3. Text your maintenance plans, ex: DailyBackup. Select Option button : Single Schedule for entire task.

  4. Click Configure > Set Schedule according your demands.

  5. Select Maintenance tasks. for this case, you choose Full Database backup.

  6. Click Next, then define which database to backup, set backup location, and backup extension.

  7. Click Next, Choose your report mode, then Finish.


Code to

DECLARE @FileName varchar(MAX)

SELECT @FileName = (SELECT 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\test1_' + CONVERT(varchar(20),GETDATE(),112) + REPLACE(CONVERT(varchar(5),GETDATE(),108),':','') + '.bak')
SELECT @FileName

BACKUP DATABASE [test] TO DISK = @FileName WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

I have refereed many blogs to automatically delete the old back ups after particular time but no one worked for me. Then I decided to create a power shell script and schedule windows job to run this script. The code for powershell script is as follows:

$backuplimit = (Get-Date).AddMinutes(-15)
$path = "F:\BackUp"

# Delete files older than the $limit.
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $backuplimit } | Remove-Item -Force

Hope this helps you to schedule backups of the sql databases

Comments

Post a Comment

Popular posts from this blog

Hide Ribbon on SharePoint 2013 using CSS

-
Hi Guys, I recently experienced the migration from SharePoint 2010 to 2013. I was earlier hiding Ribbon using the SPSecurityTrimmedControl and CSS. But in SP 2013 MS has divided whole ribbon in two section: s4-ribbonrow and suiteBar Hiding the ribbon is as much as easier as it was in 2010 version. But here we just need to take care of above two mentioned sections. I have gone thorough some of the blogs where people are saying that we have change the html file(Seattle.html or oslo.html) and design manger will take care of creating master page for you (ref: Hide Ribbon ). But there is another traditional way of doing same, follow the given steps: 1) Open the master page file in the SharePoint designer. 2) Add the style tag to hide the ribbon for all the user: #s4-ribbonrow, #suiteBar { display : none; } 3) Now we have to do some more changes so that at least system account can access the ribbon. Find out the <div id="s4-ribbonrow"> tag. 4) Insert the following code insi...
Read more

Configure external site as content sources in sharepoint search

-
Image
Hi, In my previous post( Creating and configuring Search service application ) I explained how to configure the search service application and its configuration. We can also search the external sites content using the SharePoint search just by creating new content source. Follow the given steps to create the new content source for external site. To get to the Manage Content Sources page Verify that the user account that is performing this procedure is a service application administrator for the Search service application. On the Home page of the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications . On the Manage Service Applications page, click Search Service Application . On the Search Administration Page, in the Crawling section, click Content Sources .                     After clicking on the link you will be redirected to the page where all the available content sources are displayed.                    To ...
Read more

Powershell: Creating and updating profile property

-
Hi Friends, I have recently create a user profile property using power shell commands. I am just sharing the code i used to create and then edit the properties like description, privacy settings, display settings etc of the property. Code to add/edit: if ((Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null ) { Add-PsSnapin Microsoft.SharePoint.PowerShell } [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") [System.Reflection.Assembly]::LoadWithPartialName("microsoft.sharepoint.portal") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server") [System.Reflection.Assembly]::LoadWithPartialName("System.Web") [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Taxonomy.dll") function CheckNull($obj, $name) { if ($obj -eq $null) { Write-Host $name "is null" -ForegroundColor Red return $true } return $false } $siteurl = "http:...
Read more