SQL Express Backup Service For Windows OS

Hi guys, as you know SQL express is not supporting agent services so we can schedule our jobs, but windows has a feature “Task Scheduler”. This is our hope :)

First of all, I create a folder on C disk, the name is DatabaseBackup than crate two folders inside of this, give name one “log”, the other “script”

Folder names and addresses are important if you don't want to modify addresses from scripts. By the way, you have to add your SQL server address to the script folder.

Create a LOG.txt inside the log folder and create 3 files in scripts folder named by “delete-bak.vbs” and “db-backup.sql” and “backup-trigger.bat”.

Let's start with SQL, this query reads all databases except master, model, temp, and msdb. It creates a loop that runs as databases counts, paste this following code into “db-backup.sql”

DECLARE @name NVARCHAR(256)
DECLARE @path NVARCHAR(512)
DECLARE @date NVARCHAR(10) = CONVERT(varchar(10),GETDATE(),120)
DECLARE @fileName NVARCHAR(512)
SET @path = 'C:\DatabaseBackup\'
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND state = 0
AND is_in_standby = 0
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '--' + @date+ '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

delete-bak.vbs file works for deleting older backups otherwise your space is decreasing day by day. (also you need this because I tried if I get back up with same name probably the older version is deleted but :) surprisingly it is not deleted, it is stacked with old one ) also in this script I added another feature if your SQL script didn't work properly, probably vb script will work properly, you will lose your last backup :) I blocked this if there is no new backup, it will not delete the old backup related with same database

On Error Resume Next  
Dim fso, folder, files, sFolder, sFolderTarget,objFile, objDictionary
Set fso = CreateObject("Scripting.FileSystemObject")  
Set objDictionary = CreateObject("Scripting.Dictionary")
sFolder = "C:\DatabaseBackup\"
Set folder = fso.GetFolder(sFolder)  
Set files = folder.Files
'creating log
Const ForAppending = 8
Set objFile = fso.OpenTextFile(sFolder & "\log\LOG.txt", ForAppending)
'creating a dictionary, add all created date difference is less than 1 day backup files 
For Each itemFiles In files 
   a=sFolder & itemFiles.Name
   b = fso.GetExtensionName(a)
  
       If uCase(b)="BAK" Then   
    
     If DateDiff("d",itemFiles.DateCreated,Now()) < 1 Then     
     arrNames = Split(itemFiles.Name, "--")
     objDictionary.Add arrNames(0), arrNames(1)  
           End If
       End If
 
Next
objFile.Write "================================================================" & VBCRLF & VBCRLF
objFile.Write "                     DATABASE BACKUP FILE REPORT                " & VBCRLF
objFile.Write "                     DATE:  " &    FormatDateTime(Now(),1)   & "" & VBCRLF
objFile.Write "                     TIME:  " &    FormatDateTime(Now(),3)   & "" & VBCRLF & VBCRLF
objFile.Write "================================================================" & VBCRLF
For Each x In files 
   a=sFolder & x.Name
   b = fso.GetExtensionName(a)
If DateDiff("d",x.DateCreated,Now()) >= 1 Then
   arrNames = Split(x.Name, "--")
   
   If objDictionary.Exists(arrNames(0)) Then
   fso.DeleteFile a
   objFile.WriteLine "BACKUP FILE DELETED: " & a
   Else
   objFile.WriteLine "BACKUP FILE DID NOT DELETED: " & a
   End If
    End If
Next 
 
 
objFile.WriteLine "================================================================" & VBCRLF & VBCRLF
objFile.Close
Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing
Set objDictionary = Nothing

Fin :) the last file is run for these, write into a bat file

@ECHO OFF
SQLCMD -s yourserverinstancename -d master -E -i "C:\DatabaseBackup\scripts\db-backup.sql"
C:\DatabaseBackup\scripts\delete-bak.vbs

save all files, if you click the bat file you will see the work :)

For automation, you have to add this bat file to your task scheduler, open it and create a basic job

BONUS: If you want to create a copy of this backup folder into a cloud follow these steps more ;)

go to https://script.google.com/home then create a new project here.

after deleting all inside and paste this

function createTimeDrivenTriggers() {
ScriptApp.newTrigger('emptyThrash')
.timeBased()
.everyHours(1)
.create();
}
function emptyThrash(){
Drive.Files.emptyTrash();
}

this script basically creates a trigger for delete one time in every hour, but currently it's not enough for work, because you should add your drive here its a kind of inheritance. You should add drive service to here

go to services and add your drive API services to here. Then press the run button, you will see your trigger in the clock symbol on the left panel.

BONUS2: Google is always in sync If the google drive size is small when the file size doubles as old and new database backups during operations. You will get a sync error from google. I add another parameter to the batch file, it will close the sync before operations then it will turn on again so it will not try to sync all, just sync to replace the backups needed

@ECHO OFF
echo ==============================================
echo         google drive sync is offline  
echo ==============================================
start taskkill /IM "googledrivesync.exe" /F
echo ==============================================
echo     sql server backup service is working
echo ==============================================
SQLCMD -s WIN-UPTOHD5OSQN\MSSQLSERVER2017 -d master -E -i "C:\DatabaseBackup\scripts\db-backup.sql"
echo ==============================================
echo     sql server backup service is completed 
echo ==============================================
echo ==============================================
echo          Deleting old backup files
echo ==============================================
C:\DatabaseBackup\scripts\delete-bak.vbs
timeout 5
echo ==============================================
echo         google drive sync is starting
echo ==============================================
cd C:\program files\google\drive
googledrivesync.exe / autostart

Happy Code..

Resources

sql script from -> https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

vbscript from -> https://www.mssqltips.com/sqlserverauthor/18/edwin-sarmiento/

google script -> https://developers.google.com/apps-script/guides/services/advanced

https://www.amixa.com/blog/2019/04/05/automatically-empty-google-drive-trash/