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
When we run this script, it will create a BAK file, is a backup file of database
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:
-U -P -i pause
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.
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.