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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s