SQL SERVER : Take database backup automatically

In this article I am going to explain how to take automate daily backup of SQL Server database

As you know database is the heart of any software driven application and nobody want to lose it on any cost. So the main purpose of the database backup is to create a copy of database that can be recovered if something goes wrong with database. There can be many reason of database failure like database got corrupt, virus.

To avoid the loss of data we need to take the database backup regularly/daily basis. It is not possible to take the backup daily manually.

The  most common ways to take database backup are Sql Jobs, Maintenance Plan and Task Scheduler. Here in this post I am going to use Task Scheduler method to take database backup automatically.

How to download Microsoft Northwind, AdventureWorks and Pubssample database for SQL Server.

First of all I have create a store procedure to take database backup.

Create proc DatabaseBackup

as begin

declare @filename varchar(500)

set @filename ='G:\Northwind_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 103), '/', '_')+'_'+ REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','_') +  '.bak'

BACKUP DATABASE Northwind TO DISK =@filename

end

Now create a Sql file (e.g. Northwind_DatabaseBackup.sql) and execute the database backup store procedure.

EXEC  Northwind.dbo.DatabaseBackup

After that create a batch file. I have create a .bat file named it as Northwind_DatabaseBackup.bat. Write the sql server command line to run the .sql file. How to use sqlcmd utility you can check on these links sqlcmdUtility and sqlcmd - Use the utility.

sqlcmd -i C:\task\Northwind_DatabaseBackup.sql

I have create a folder task and keep both files in this folder.

Now to you have to open the Task scheduler and create abasic task to take backup automatically.

Check this article to know about Window Task Scheduler.

This article to create a basic task in Windows Task Scheduler