SQL Express is widely used but has huge downside, there is no SQL Agent available. Even Windows internal databases, especially WSUS / Windows Update Services / Microsoft Update Services are in the end SQL Express like databases that do not have a SQL Agent.
Now, you can have central SQL Servers with Agents have them backed up – and I recommend on doing so if possible. But for the many times this is not possible, you will need to find another way to create those nice little .BAK files for SQL internal backups aka. SQL Maintenance Plan Backups. To work around this issue, I once wrote a script that automates this for each database found on a specific SQL server. It creates the backups via SQLCMD commands and even does a clean up of obsolete files (files older than x days), almost like SQL Maintenance Plans do it.
The script is divided in to a .CMD file that executes the actual backup and where you set the configuration/parameters and a .VBS file that is controlled by the actual .CMD script and will perform the backup cleanup. In the end you can have the .CMD send a email report – I used the SMTPSEND program from Michael Kocum (https://www.dataenter.com/download.asp) for this since I already ad it flying around – you could replace the mail send option with another prepared SMTPSEND client, a VBS script or just remove it completely.
Adjusting the settings / parameter:
This is all done in the SQLBACKUP.CMD file – the header section pretty much will explain all you need to know, from SQL Server to SQL-User and Password over Mail-Server to recipients.
If you want to execute the SQLBackups as the Windows-User that is executing the script, you need to exchange the REM (remarks) for the following two lines further down in the scripts. I apologize for the inconvenience, this is a old script I never updated to have those settings in the header (more automated), I always just changed the lines.
1 2 3 4 | REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%" REM the following line uses Windows User authentication - remove the REM if you want to use this instead REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%" |
Everything else should be rather easy. Of course you will need sufficient access rights to the SQL databases and your destination backup folder. The task-scheduler might work best if you execute the script with “cmd /c c:\scripts\sqlbackup.cmd” (change the path as you need it) and set the working directory / startup folder right. It might help to execute the task with elevated rights etc. – all depending on your systems configuration.
Below are the two scripts – I hope this helps some of you. The generated .BAK files can simply be restored in SQL services via the GUI cause they are native SQL backup files.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | @ECHO OFF SETLOCAL REM Variables - please adjust REM ============================================================================================= REM Server or Server\Instance SET SQLServer=SERVER\INSTANANCEifNEEDED REM local Path or UNC Path (must be accessible under the executing user!) SET SQLBackupPath=D:\Backup\SubFolderName----\\SERVER\SHARE\FOLDER-----EitherLocalOrUNC REM max age the files can be in days before they get deleted SET SQLBackupMaxAge=1 SET SQLUser=sa SET SQLPW= SET MailServer=relay.domain.local SET MailServerPort=25 SET MailFrom=sqlbackup@domain.com SET MailTo=backups@domain.com SET MailSubject=SQL Express Backup (INSTANCENAME/DESCRIPTION) REM ============================================================================================= REM Script-System-Variables - do not change after this point! SET ScriptLog=%~dp0\SQLBackup_Script_Log.txt REM Running Script DEL %ScriptLog% ECHO Script-Log Date: %Date% / Time: %Time% >> %ScriptLog% ECHO Script-Parameter: >> %ScriptLog% ECHO =========================================================================== >> %ScriptLog% ECHO SQLServer: %SQLServer% >> %ScriptLog% ECHO SQLBackupPath: %SQLBackupPath% >> %ScriptLog% ECHO SQLBackupMaxAge: %SQLBackupMaxAge% >> %ScriptLog% ECHO SQLUser: %SQLUser% >> %ScriptLog% ECHO SQLPW: %SQLPW% >> %ScriptLog% ECHO MailServer: %MailServer% >> %ScriptLog% ECHO MailServerPort: %MailServerPort% >> %ScriptLog% ECHO MailFrom: %MailFrom% >> %ScriptLog% ECHO MailTo: %MailTo% >> %ScriptLog% ECHO MailSubject: %MailSubject% >> %ScriptLog% ECHO =========================================================================== >> %ScriptLog% ECHO Script executing: >> %ScriptLog% ECHO Delete old Backup Info File >> %ScriptLog% DEL %SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog% ECHO =========================================================================== >> %ScriptLog% ECHO Get date in format YYYY-MM-DD (assumes the locale is the United States) >> %ScriptLog% FOR /F "tokens=1,2,3,4 delims=. " %%A IN ('Date /T') DO SET NowDate=%%C-%%B-%%A >> %ScriptLog% FOR /F "tokens=1,2 delims=: " %%A IN ('Time /T') DO SET NowDate=%NowDate%_%%A-%%B >> %ScriptLog% ECHO =========================================================================== >> %ScriptLog% ECHO Build a list of databases to backup >> %ScriptLog% SET DBList=%SQLBackupPath%\SQLDBList.txt REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%" REM the following line uses Windows User authentication - remove the REM if you want to use this instead REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%" ECHO =========================================================================== >> %ScriptLog% ECHO Backup each database, prepending the date to the filename >> %ScriptLog% FOR /F "tokens=*" %%I IN (%DBList%) DO ( ECHO Backing up database: %%I >> %ScriptLog% ECHO Backing up database: %%I >>%SQLBackupPath%\SQLBackupStatus.txt ECHO Destination: %SQLBackupPath%\%NowDate%_%%I.bak >>%SQLBackupPath%\SQLBackupStatus.txt SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -Q "BACKUP DATABASE [%%I] TO Disk='%SQLBackupPath%\ %NowDate%_%%I.bak'" >>%SQLBackupPath%\SQLBackupStatus.txt ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt ) ECHO =========================================================================== >> %ScriptLog% ECHO Clean up the temp file >> %ScriptLog% IF EXIST "%DBList%" DEL /F /Q "%DBList%" >> %ScriptLog% ECHO =========================================================================== >> %ScriptLog% ECHO Old Backups Clean Up >> %ScriptLog% CSCRIPT %~dp0\SQLBackup.vbs %SQLBackupMaxAge% "%SQLBackupPath%" >> %ScriptLog% ECHO SQL Backup CleanUp executed >>%SQLBackupPath%\SQLBackupStatus.txt ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt ECHO Currently available Backup-Files: >>%SQLBackupPath%\SQLBackupStatus.txt DIR %SQLBackupPath% >>%SQLBackupPath%\SQLBackupStatus.txt ECHO =========================================================================== >> %ScriptLog% ECHO Send Info Mail >> %ScriptLog% %~dp0\SMTPSEND -f%MailFrom% -t%MailTo% -h%MailServer% -p%MailServerPort% -s%MailSubject% -i%SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog% ECHO =========================================================================== >> %ScriptLog% ENDLOCAL |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | Set objArgs = WScript.Arguments If objArgs.Count > 0 Then Dim strPath Dim intAge intAge = objArgs(0) strPath = objArgs(1) Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder(strPath) For Each objFile in objFolder.Files If DateDiff("d", objFile.DateLastModified, Now()) > Cint(intAge) Then On Error Resume Next objFile.Delete On Error Goto 0 End If Next End If |
