Tuesday, October 15, 2019

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.

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


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.

No comments:

Post a Comment