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
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.
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.
What do you think about this article?
If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.