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


  • 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


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

Published by Mohit Vashishtha

Having extensive experience in software industry as a Team Lead and SharePoint consultant. I have worked with various MS technologies like SharePoint, .Net, AngularJS and Angular 6. I love to learn and share new things which I learn during my experience.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: