Auto Backup Of Database in SQL Server 2008 & Above

Introduction

This tip refers to Auto Backup Of Database in SQL Server 2008 & above which helps you to get rid of making a Backup (.bak) file of database manually day to day.

Background

To schedule an Auto Backup, you need to know the version of SQL installed on your system along with how to operate the task scheduler with default application in Windows.

Using the Code

It's a simple way to implement Auto Backup, you need to write a Query or follow the given one for making a Backup of specific database in SQL Server. Save the SQL script.

//
BACKUP DATABASE [DBSample] TO  DISK = N'C:\database\xxxxxxx.bak' WITH NOFORMAT, NOINIT,  _
NAME = N'DBSample Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
// 

After following this, make a Batch file with the given lines of code:

//
@ECHO OFF
ECHO. Creating Backupc of database
cd/
cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\
SQLCMD.EXE -U sa -P pass -S XXXX-PC\LOCALHOST -i "C:\database\Sql Auto Backup\script_sql.sql"
cd/
cd C:\database\
COPY "C:\database\dbsample_backup.bak" _
"C:\database\Sql Backup\dbsample_backup%date:~-10,2%%date:~-7,2%%date:~-4,4%.bak"
C:\Backup\Sql Backup\dbsample_backup.bak"  
//

To proceed further on given code, check the SQLCMD.EXE path in program files and make change as per version of SQL installed on your system.

If SQL Server Authentication is enabled, give the UserName and Password and your Database Engine Name.

Set path of SQL script which we made up above.

"C:\database\Sql Auto Backup\script_sql.sql"

SQLCMD.EXE -U sa -P pass -S XXXX-PC\LOCALHOST -i "C:\database\Sql Auto Backup\script_sql.sql"

This snippet makes a bak file and makes a copy on given path and backup file comes with current date and time.

COPY "C:\database\dbsample_backup.bak" 
"C:\database\Sql Backup\dbsample_backup%date:~-10,2%%date:~-7,2%%date:~-4,4%.bak"

Now save the Batch File at the same place where script is saved possibly.

Run Task Scheduler by pressing start button Search as Task Scheduler.

Go to Task Scheduler library (left side window), right click on window. Go to Task Scheduler library (left side window), right click on window.

Create Task > in General Tab, give the Name to your Task.

Go to Action, press new button at left bottom and browse the batch file.

Set the batch file path and click ok.

Check the setting daily and set the time on which back up process has to be started. Go To trigger tab, create new trigger.

!! Enjoy !!