Automatically back up Database to disk


How to automatically backup your database at a specific time? This article will present an easy and simple approach.
How to automatically backup your database at a specific time? This article will present an easy and simple approach.

Written by Do Minh Tuan


Posted at 14 Feb 2018, 11:37 PM



Sometime we need to back up our database. It's a mandatory job that any system has to do, to prevent losing data of customer or some potentional risks from attacking. This topic is very popular and attract developer's attention. The most common way can be found is from Microsoft Support. I'm gonna guild you the same, but not really the same. Or you can try SQL Server Agent, but seem that it have to wait to a specific time to back the DB up. The idea is not only a way to back up it at a specific time daily, but also at anytime we want.

Step 1: Create an SQL file that contains SQL Query to backup your database

Create a *.sql file, name it script_backup_db.sql (or whatever you want, but remember its name).

Open it and paste the following code:

DECLARE @DATE INT, @MONTH INT, @YEAR INT, @HOUR VARCHAR(12), @path VARCHAR(2000)
 
SELECT @DATE = DAY(GETDATE()),
@MONTH = MONTH(GETDATE()),
@YEAR = YEAR(GETDATE()),
@path = '\_' + CAST(@DATE AS VARCHAR(5)) + '_' + CAST(@MONTH AS VARCHAR(5)) + '_' + CAST(@YEAR AS VARCHAR(5))
 + '_' + FORMAT(GETDATE(),'hh_mm_ss_tt', 'en-us') + '.bak'
 
 
PRINT 'Backing up the latest version of  to disk ' + @path + ' ... ';
 
BACKUP DATABASE  TO DISK = @path GO

 

Where is the path to the folder on your physical disk that you want to save the backup file. For example: D:\BackupDatabase

is your ... Database name, of course.

When we run this script, it will create a BAK file, is a backup file of database , and save it to  with name: _, where hh:mm:ss is the time you created this backup file.

So, assuming I want to backup the database named "Customers" to folder D:\BackupDatabase, my .sql file should be like:

DECLARE @DATE INT, @MONTH INT, @YEAR INT, @HOUR VARCHAR(12), @path VARCHAR(2000)
 
SELECT @DATE = DAY(GETDATE()),
@MONTH = MONTH(GETDATE()),
@YEAR = YEAR(GETDATE()),
@path = 'D:\BackupDatabase\Customers_' + CAST(@DATE AS VARCHAR(5)) + '_' + CAST(@MONTH AS VARCHAR(5)) + '_' + CAST(@YEAR AS VARCHAR(5))
 + '_' + FORMAT(GETDATE(),'hh_mm_ss_tt', 'en-us') + '.bak'
 
 
PRINT 'Backing up the latest version of Customers to disk ' + @path + ' ... ';
 
BACKUP DATABASE Customers
TO DISK = @path
GO


If I run this script, in folder D:\BackupDatabase, I will get the file name: Customers_12_00_00_pm (if I run at 12:00:00 PM, the time included within the file name might change based on the time we run this script).

Step 2: create a BAT file

Save your .sql file. In the same folder (for easy, of course you can choose any folder), create an *.bat file, name it backup db.bat (or any name you want).

So now my folder should have 2 files.

Open bat file (with notepad, Notepad++ or any editor you have). Type in: 

sqlcmd -S  -U  -P   -i 
pause

 Where is an instance of your database, for example: MY_PC\SQLEXPRESS, or just .

, like sa

is your password of the user above

is the path to the *.sql file you saved. In this tutorial, I guilded you to save them in the same folder, so it should be in the same folder with bat file (for easy to edit both of them later, if your DB configurations changed).

Save the bat file.

From now on, instead of opening SQL Management Studio, wait for it to start up, and slowly click "New Query" and run the script in the *.sql file, we just only need to run the BAT file. 

BUT KEEP IN MIND: KEEP THIS BAT FILE IN A SAFE FOLDER, BECAUSE IT CONTAINS YOUR DATABASE USERNAME AND PASSWORD AS PLAIN TEXT 

 Step 3: Schedule to run BAT file

We all know when we run BAT file, it will create a backup file to the folder specified. So instead of finding a way to automatically backup your DB, we will find the way to automatically run the BAT file at a specific time. Not a bad idea. 

Kết quả hình ảnh cho not bad

3.1. Open Task Scheduler, it is a default app on Windows.

3.2. Click "Create task" and give it a name, for example: "auto backup DB at 5pm"

3.3. Choose "Run whether user is logged on or not"

3.4. Configure for: choose your current operating system version.

3.5. Click "Trigger" task. Trigger is a "timer" that we will setup the time to active an event.

3.5.1. Click "New" and choose "Daily". Then set the start time from that the scheduler will execute your task. If you want to backup everyday at 5:00 PM, choose 5:00:00 PM and Recur every 1 days.

3.5.2. If you want to add some advanced settings, try to learn more about Task Scheduler, and control your trigger.

3.5.3. Now click "OK" and in trigger list, you should see an event with this information:

"Daily - At 5:00 PM everyday - Enabled"

3.6. Click "Action" task. If trigger sets up the time to do the task ("when to do"), "Action" will set up the task ("what to do").

3.6.1. In "Action", make sure we are selecting "Start a program"

3.6.2. Click "Browse" and browse to your BAT file

3.6.3. Click "OK"

3.7. Click "OK" to complete. You might be asked to enter password off your PC to confirm (if you are using password to log in).

3.8. Now you can see in the Task list, our task is appeared. Just click "RUN" to start it. 

3.9. We can also disable or delete the task.

That's all to configure, set up and schedule our backup job automatically at the time specific. I hope this trick will help anyone who is looking for the solution.



Comments


Search